<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<!--[if IE]><meta http-equiv="X-UA-Compatible" content="IE=edge"><![endif]-->
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="generator" content="Asciidoctor 1.5.4-dev">
<title>BeetlSQL User Guide</title>
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Open+Sans:300,300italic,400,400italic,600,600italic%7CNoto+Serif:400,400italic,700,700italic%7CDroid+Sans+Mono:400,700">
<style>
/*! normalize.css v2.1.2 | MIT License | git.io/normalize */
/* ========================================================================== HTML5 display definitions ========================================================================== */
/** Correct `block` display not defined in IE 8/9. */
article, aside, details, figcaption, figure, footer, header, hgroup, main, nav, section, summary {
    display: block;
}

/** Correct `inline-block` display not defined in IE 8/9. */
audio, canvas, video {
    display: inline-block;
}

/** Prevent modern browsers from displaying `audio` without controls. Remove excess height in iOS 5 devices. */
audio:not([controls]) {
    display: none;
    height: 0;
}

/** Address `[hidden]` styling not present in IE 8/9. Hide the `template` element in IE, Safari, and Firefox < 22. */
[hidden], template {
    display: none;
}

script {
    display: none !important;
}

/* ========================================================================== Base ========================================================================== */
/** 1. Set default font family to sans-serif. 2. Prevent iOS text size adjust after orientation change, without disabling user zoom. */
html {
    font-family: sans-serif; /* 1 */
    -ms-text-size-adjust: 100%; /* 2 */
    -webkit-text-size-adjust: 100%; /* 2 */
}

/** Remove default margin. */
body {
    margin: 0;
}

/* ========================================================================== Links ========================================================================== */
/** Remove the gray background color from active links in IE 10. */
a {
    background: transparent;
}

/** Address `outline` inconsistency between Chrome and other browsers. */
a:focus {
    outline: thin dotted;
}

/** Improve readability when focused and also mouse hovered in all browsers. */
a:active, a:hover {
    outline: 0;
}

/* ========================================================================== Typography ========================================================================== */
/** Address variable `h1` font-size and margin within `section` and `article` contexts in Firefox 4+, Safari 5, and Chrome. */
h1 {
    font-size: 2em;
    margin: 0.67em 0;
}

/** Address styling not present in IE 8/9, Safari 5, and Chrome. */
abbr[title] {
    border-bottom: 1px dotted;
}

/** Address style set to `bolder` in Firefox 4+, Safari 5, and Chrome. */
b, strong {
    font-weight: bold;
}

/** Address styling not present in Safari 5 and Chrome. */
dfn {
    font-style: italic;
}

/** Address differences between Firefox and other browsers. */
hr {
    -moz-box-sizing: content-box;
    box-sizing: content-box;
    height: 0;
}

/** Address styling not present in IE 8/9. */
mark {
    background: #ff0;
    color: #000;
}

/** Correct font family set oddly in Safari 5 and Chrome. */
code, kbd, pre, samp {
    font-family: monospace, serif;
    font-size: 1em;
}

/** Improve readability of pre-formatted text in all browsers. */
pre {
    white-space: pre-wrap;
}

/** Set consistent quote types. */
q {
    quotes: "\201C" "\201D" "\2018" "\2019";
}

/** Address inconsistent and variable font size in all browsers. */
small {
    font-size: 80%;
}

/** Prevent `sub` and `sup` affecting `line-height` in all browsers. */
sub, sup {
    font-size: 75%;
    line-height: 0;
    position: relative;
    vertical-align: baseline;
}

sup {
    top: -0.5em;
}

sub {
    bottom: -0.25em;
}

/* ========================================================================== Embedded content ========================================================================== */
/** Remove border when inside `a` element in IE 8/9. */
img {
    border: 0;
}

/** Correct overflow displayed oddly in IE 9. */
svg:not(:root) {
    overflow: hidden;
}

/* ========================================================================== Figures ========================================================================== */
/** Address margin not present in IE 8/9 and Safari 5. */
figure {
    margin: 0;
}

/* ========================================================================== Forms ========================================================================== */
/** Define consistent border, margin, and padding. */
fieldset {
    border: 1px solid #c0c0c0;
    margin: 0 2px;
    padding: 0.35em 0.625em 0.75em;
}

/** 1. Correct `color` not being inherited in IE 8/9. 2. Remove padding so people aren't caught out if they zero out fieldsets. */
legend {
    border: 0; /* 1 */
    padding: 0; /* 2 */
}

/** 1. Correct font family not being inherited in all browsers. 2. Correct font size not being inherited in all browsers. 3. Address margins set differently in Firefox 4+, Safari 5, and Chrome. */
button, input, select, textarea {
    font-family: inherit; /* 1 */
    font-size: 100%; /* 2 */
    margin: 0; /* 3 */
}

/** Address Firefox 4+ setting `line-height` on `input` using `!important` in the UA stylesheet. */
button, input {
    line-height: normal;
}

/** Address inconsistent `text-transform` inheritance for `button` and `select`. All other form control elements do not inherit `text-transform` values. Correct `button` style inheritance in Chrome, Safari 5+, and IE 8+. Correct `select` style inheritance in Firefox 4+ and Opera. */
button, select {
    text-transform: none;
}

/** 1. Avoid the WebKit bug in Android 4.0.* where (2) destroys native `audio` and `video` controls. 2. Correct inability to style clickable `input` types in iOS. 3. Improve usability and consistency of cursor style between image-type `input` and others. */
button, html input[type="button"], input[type="reset"], input[type="submit"] {
    -webkit-appearance: button; /* 2 */
    cursor: pointer; /* 3 */
}

/** Re-set default cursor for disabled elements. */
button[disabled], html input[disabled] {
    cursor: default;
}

/** 1. Address box sizing set to `content-box` in IE 8/9. 2. Remove excess padding in IE 8/9. */
input[type="checkbox"], input[type="radio"] {
    box-sizing: border-box; /* 1 */
    padding: 0; /* 2 */
}

/** 1. Address `appearance` set to `searchfield` in Safari 5 and Chrome. 2. Address `box-sizing` set to `border-box` in Safari 5 and Chrome (include `-moz` to future-proof). */
input[type="search"] {
    -webkit-appearance: textfield; /* 1 */
    -moz-box-sizing: content-box;
    -webkit-box-sizing: content-box; /* 2 */
    box-sizing: content-box;
}

/** Remove inner padding and search cancel button in Safari 5 and Chrome on OS X. */
input[type="search"]::-webkit-search-cancel-button, input[type="search"]::-webkit-search-decoration {
    -webkit-appearance: none;
}

/** Remove inner padding and border in Firefox 4+. */
button::-moz-focus-inner, input::-moz-focus-inner {
    border: 0;
    padding: 0;
}

/** 1. Remove default vertical scrollbar in IE 8/9. 2. Improve readability and alignment in all browsers. */
textarea {
    overflow: auto; /* 1 */
    vertical-align: top; /* 2 */
}

/* ========================================================================== Tables ========================================================================== */
/** Remove most spacing between table cells. */
table {
    border-collapse: collapse;
    border-spacing: 0;
}

meta.foundation-mq-small {
    font-family: "only screen and (min-width: 768px)";
    width: 768px;
}

meta.foundation-mq-medium {
    font-family: "only screen and (min-width:1280px)";
    width: 1280px;
}

meta.foundation-mq-large {
    font-family: "only screen and (min-width:1440px)";
    width: 1440px;
}

*, *:before, *:after {
    -moz-box-sizing: border-box;
    -webkit-box-sizing: border-box;
    box-sizing: border-box;
}

html, body {
    font-size: 100%;
}

body {
    background: white;
    color: rgba(0, 0, 0, 0.8);
    padding: 0;
    margin: 0;
    font-family: "Noto Serif", "DejaVu Serif", serif;
    font-weight: normal;
    font-style: normal;
    line-height: 1;
    position: relative;
    cursor: auto;
}

a:hover {
    cursor: pointer;
}

img, object, embed {
    max-width: 100%;
    height: auto;
}

object, embed {
    height: 100%;
}

img {
    -ms-interpolation-mode: bicubic;
}

#map_canvas img, #map_canvas embed, #map_canvas object, .map_canvas img, .map_canvas embed, .map_canvas object {
    max-width: none !important;
}

.left {
    float: left !important;
}

.right {
    float: right !important;
}

.text-left {
    text-align: left !important;
}

.text-right {
    text-align: right !important;
}

.text-center {
    text-align: center !important;
}

.text-justify {
    text-align: justify !important;
}

.hide {
    display: none;
}

.antialiased, body {
    -webkit-font-smoothing: antialiased;
}

img {
    display: inline-block;
    vertical-align: middle;
}

textarea {
    height: auto;
    min-height: 50px;
}

select {
    width: 100%;
}

p.lead, .paragraph.lead > p, #preamble > .sectionbody > .paragraph:first-of-type p {
    font-size: 1.21875em;
    line-height: 1.6;
}

.subheader, .admonitionblock td.content > .title, .audioblock > .title, .exampleblock > .title, .imageblock > .title, .listingblock > .title, .literalblock > .title, .stemblock > .title, .openblock > .title, .paragraph > .title, .quoteblock > .title, table.tableblock > .title, .verseblock > .title, .videoblock > .title, .dlist > .title, .olist > .title, .ulist > .title, .qlist > .title, .hdlist > .title {
    line-height: 1.45;
    color: #7a2518;
    font-weight: normal;
    margin-top: 0;
    margin-bottom: 0.25em;
}

/* Typography resets */
div, dl, dt, dd, ul, ol, li, h1, h2, h3, #toctitle, .sidebarblock > .content > .title, h4, h5, h6, pre, form, p, blockquote, th, td {
    margin: 0;
    padding: 0;
    direction: ltr;
}

/* Default Link Styles */
a {
    color: #2156a5;
    text-decoration: underline;
    line-height: inherit;
}

a:hover, a:focus {
    color: #1d4b8f;
}

a img {
    border: none;
}

/* Default paragraph styles */
p {
    font-family: inherit;
    font-weight: normal;
    font-size: 1em;
    line-height: 1.6;
    margin-bottom: 1.25em;
    text-rendering: optimizeLegibility;
}

p aside {
    font-size: 0.875em;
    line-height: 1.35;
    font-style: italic;
}

/* Default header styles */
h1, h2, h3, #toctitle, .sidebarblock > .content > .title, h4, h5, h6 {
    font-family: "Open Sans", "DejaVu Sans", sans-serif;
    font-weight: 300;
    font-style: normal;
    color: #ba3925;
    text-rendering: optimizeLegibility;
    margin-top: 1em;
    margin-bottom: 0.5em;
    line-height: 1.0125em;
}

h1 small, h2 small, h3 small, #toctitle small, .sidebarblock > .content > .title small, h4 small, h5 small, h6 small {
    font-size: 60%;
    color: #e99b8f;
    line-height: 0;
}

h1 {
    font-size: 2.125em;
}

h2 {
    font-size: 1.6875em;
}

h3, #toctitle, .sidebarblock > .content > .title {
    font-size: 1.375em;
}

h4 {
    font-size: 1.125em;
}

h5 {
    font-size: 1.125em;
}

h6 {
    font-size: 1em;
}

hr {
    border: solid #ddddd8;
    border-width: 1px 0 0;
    clear: both;
    margin: 1.25em 0 1.1875em;
    height: 0;
}

/* Helpful Typography Defaults */
em, i {
    font-style: italic;
    line-height: inherit;
}

strong, b {
    font-weight: bold;
    line-height: inherit;
}

small {
    font-size: 60%;
    line-height: inherit;
}

code {
    font-family: "Droid Sans Mono", "DejaVu Sans Mono", "Monospace", monospace;
    font-weight: normal;
    color: rgba(0, 0, 0, 0.9);
}

/* Lists */
ul, ol, dl {
    font-size: 1em;
    line-height: 1.6;
    margin-bottom: 1.25em;
    list-style-position: outside;
    font-family: inherit;
}

ul, ol {
    margin-left: 1.5em;
}

ul.no-bullet, ol.no-bullet {
    margin-left: 1.5em;
}

/* Unordered Lists */
ul li ul, ul li ol {
    margin-left: 1.25em;
    margin-bottom: 0;
    font-size: 1em; /* Override nested font-size change */
}

ul.square li ul, ul.circle li ul, ul.disc li ul {
    list-style: inherit;
}

ul.square {
    list-style-type: square;
}

ul.circle {
    list-style-type: circle;
}

ul.disc {
    list-style-type: disc;
}

ul.no-bullet {
    list-style: none;
}

/* Ordered Lists */
ol li ul, ol li ol {
    margin-left: 1.25em;
    margin-bottom: 0;
}

/* Definition Lists */
dl dt {
    margin-bottom: 0.3125em;
    font-weight: bold;
}

dl dd {
    margin-bottom: 1.25em;
}

/* Abbreviations */
abbr, acronym {
    text-transform: uppercase;
    font-size: 90%;
    color: rgba(0, 0, 0, 0.8);
    border-bottom: 1px dotted #dddddd;
    cursor: help;
}

abbr {
    text-transform: none;
}

/* Blockquotes */
blockquote {
    margin: 0 0 1.25em;
    padding: 0.5625em 1.25em 0 1.1875em;
    border-left: 1px solid #dddddd;
}

blockquote cite {
    display: block;
    font-size: 0.9375em;
    color: rgba(0, 0, 0, 0.6);
}

blockquote cite:before {
    content: "\2014 \0020";
}

blockquote cite a, blockquote cite a:visited {
    color: rgba(0, 0, 0, 0.6);
}

blockquote, blockquote p {
    line-height: 1.6;
    color: rgba(0, 0, 0, 0.85);
}

/* Microformats */
.vcard {
    display: inline-block;
    margin: 0 0 1.25em 0;
    border: 1px solid #dddddd;
    padding: 0.625em 0.75em;
}

.vcard li {
    margin: 0;
    display: block;
}

.vcard .fn {
    font-weight: bold;
    font-size: 0.9375em;
}

.vevent .summary {
    font-weight: bold;
}

.vevent abbr {
    cursor: auto;
    text-decoration: none;
    font-weight: bold;
    border: none;
    padding: 0 0.0625em;
}

@media only screen and (min-width: 768px) {
    h1, h2, h3, #toctitle, .sidebarblock > .content > .title, h4, h5, h6 {
        line-height: 1.2;
    }

    h1 {
        font-size: 2.75em;
    }

    h2 {
        font-size: 2.3125em;
    }

    h3, #toctitle, .sidebarblock > .content > .title {
        font-size: 1.6875em;
    }

    h4 {
        font-size: 1.4375em;
    }
}

/* Tables */
table {
    background: white;
    margin-bottom: 1.25em;
    border: solid 1px #dedede;
}

table thead, table tfoot {
    background: #f7f8f7;
    font-weight: bold;
}

table thead tr th, table thead tr td, table tfoot tr th, table tfoot tr td {
    padding: 0.5em 0.625em 0.625em;
    font-size: inherit;
    color: rgba(0, 0, 0, 0.8);
    text-align: left;
}

table tr th, table tr td {
    padding: 0.5625em 0.625em;
    font-size: inherit;
    color: rgba(0, 0, 0, 0.8);
}

table tr.even, table tr.alt, table tr:nth-of-type(even) {
    background: #f8f8f7;
}

table thead tr th, table tfoot tr th, table tbody tr td, table tr td, table tfoot tr td {
    display: table-cell;
    line-height: 1.6;
}

h1, h2, h3, #toctitle, .sidebarblock > .content > .title, h4, h5, h6 {
    line-height: 1.2;
    word-spacing: -0.05em;
}

h1 strong, h2 strong, h3 strong, #toctitle strong, .sidebarblock > .content > .title strong, h4 strong, h5 strong, h6 strong {
    font-weight: 400;
}

.clearfix:before, .clearfix:after, .float-group:before, .float-group:after {
    content: " ";
    display: table;
}

.clearfix:after, .float-group:after {
    clear: both;
}

*:not(pre) > code {
    font-size: 0.9375em;
    font-style: normal !important;
    letter-spacing: 0;
    padding: 0.1em 0.5ex;
    word-spacing: -0.15em;
    background-color: #f7f7f8;
    -webkit-border-radius: 4px;
    border-radius: 4px;
    line-height: 1.45;
    text-rendering: optimizeSpeed;
}

pre, pre > code {
    line-height: 1.45;
    color: rgba(0, 0, 0, 0.9);
    font-family: "Droid Sans Mono", "DejaVu Sans Mono", "Monospace", monospace;
    font-weight: normal;
    text-rendering: optimizeSpeed;
}

.keyseq {
    color: rgba(51, 51, 51, 0.8);
}

kbd {
    display: inline-block;
    color: rgba(0, 0, 0, 0.8);
    font-size: 0.75em;
    line-height: 1.4;
    background-color: #f7f7f7;
    border: 1px solid #ccc;
    -webkit-border-radius: 3px;
    border-radius: 3px;
    -webkit-box-shadow: 0 1px 0 rgba(0, 0, 0, 0.2), 0 0 0 0.1em white inset;
    box-shadow: 0 1px 0 rgba(0, 0, 0, 0.2), 0 0 0 0.1em white inset;
    margin: -0.15em 0.15em 0 0.15em;
    padding: 0.2em 0.6em 0.2em 0.5em;
    vertical-align: middle;
    white-space: nowrap;
}

.keyseq kbd:first-child {
    margin-left: 0;
}

.keyseq kbd:last-child {
    margin-right: 0;
}

.menuseq, .menu {
    color: rgba(0, 0, 0, 0.8);
}

b.button:before, b.button:after {
    position: relative;
    top: -1px;
    font-weight: normal;
}

b.button:before {
    content: "[";
    padding: 0 3px 0 2px;
}

b.button:after {
    content: "]";
    padding: 0 2px 0 3px;
}

p a > code:hover {
    color: rgba(0, 0, 0, 0.9);
}

#header, #content, #footnotes, #footer {
    width: 100%;
    margin-left: auto;
    margin-right: auto;
    margin-top: 0;
    margin-bottom: 0;
    max-width: 62.5em;
    *zoom: 1;
    position: relative;
    padding-left: 0.9375em;
    padding-right: 0.9375em;
}

#header:before, #header:after, #content:before, #content:after, #footnotes:before, #footnotes:after, #footer:before, #footer:after {
    content: " ";
    display: table;
}

#header:after, #content:after, #footnotes:after, #footer:after {
    clear: both;
}

#content {
    margin-top: 1.25em;
}

#content:before {
    content: none;
}

#header > h1:first-child {
    color: rgba(0, 0, 0, 0.85);
    margin-top: 2.25rem;
    margin-bottom: 0;
}

#header > h1:first-child + #toc {
    margin-top: 8px;
    border-top: 1px solid #ddddd8;
}

#header > h1:only-child, body.toc2 #header > h1:nth-last-child(2) {
    border-bottom: 1px solid #ddddd8;
    padding-bottom: 8px;
}

#header .details {
    border-bottom: 1px solid #ddddd8;
    line-height: 1.45;
    padding-top: 0.25em;
    padding-bottom: 0.25em;
    padding-left: 0.25em;
    color: rgba(0, 0, 0, 0.6);
    display: -ms-flexbox;
    display: -webkit-flex;
    display: flex;
    -ms-flex-flow: row wrap;
    -webkit-flex-flow: row wrap;
    flex-flow: row wrap;
}

#header .details span:first-child {
    margin-left: -0.125em;
}

#header .details span.email a {
    color: rgba(0, 0, 0, 0.85);
}

#header .details br {
    display: none;
}

#header .details br + span:before {
    content: "\00a0\2013\00a0";
}

#header .details br + span.author:before {
    content: "\00a0\22c5\00a0";
    color: rgba(0, 0, 0, 0.85);
}

#header .details br + span#revremark:before {
    content: "\00a0|\00a0";
}

#header #revnumber {
    text-transform: capitalize;
}

#header #revnumber:after {
    content: "\00a0";
}

#content > h1:first-child:not([class]) {
    color: rgba(0, 0, 0, 0.85);
    border-bottom: 1px solid #ddddd8;
    padding-bottom: 8px;
    margin-top: 0;
    padding-top: 1rem;
    margin-bottom: 1.25rem;
}

#toc {
    border-bottom: 1px solid #efefed;
    padding-bottom: 0.5em;
}

#toc > ul {
    margin-left: 0.125em;
}

#toc ul.sectlevel0 > li > a {
    font-style: italic;
}

#toc ul.sectlevel0 ul.sectlevel1 {
    margin: 0.5em 0;
}

#toc ul {
    font-family: "Open Sans", "DejaVu Sans", sans-serif;
    list-style-type: none;
}

#toc a {
    text-decoration: none;
}

#toc a:active {
    text-decoration: underline;
}

#toctitle {
    color: #7a2518;
    font-size: 1.2em;
}

@media only screen and (min-width: 768px) {
    #toctitle {
        font-size: 1.375em;
    }

    body.toc2 {
        padding-left: 15em;
        padding-right: 0;
    }

    #toc.toc2 {
        margin-top: 0 !important;
        background-color: #f8f8f7;
        position: fixed;
        width: 15em;
        left: 0;
        top: 0;
        border-right: 1px solid #efefed;
        border-top-width: 0 !important;
        border-bottom-width: 0 !important;
        z-index: 1000;
        padding: 1.25em 1em;
        height: 100%;
        overflow: auto;
    }

    #toc.toc2 #toctitle {
        margin-top: 0;
        font-size: 1.2em;
    }

    #toc.toc2 > ul {
        font-size: 0.9em;
        margin-bottom: 0;
    }

    #toc.toc2 ul ul {
        margin-left: 0;
        padding-left: 1em;
    }

    #toc.toc2 ul.sectlevel0 ul.sectlevel1 {
        padding-left: 0;
        margin-top: 0.5em;
        margin-bottom: 0.5em;
    }

    body.toc2.toc-right {
        padding-left: 0;
        padding-right: 15em;
    }

    body.toc2.toc-right #toc.toc2 {
        border-right-width: 0;
        border-left: 1px solid #efefed;
        left: auto;
        right: 0;
    }
}

@media only screen and (min-width: 1280px) {
    body.toc2 {
        padding-left: 20em;
        padding-right: 0;
    }

    #toc.toc2 {
        width: 20em;
    }

    #toc.toc2 #toctitle {
        font-size: 1.375em;
    }

    #toc.toc2 > ul {
        font-size: 0.95em;
    }

    #toc.toc2 ul ul {
        padding-left: 1.25em;
    }

    body.toc2.toc-right {
        padding-left: 0;
        padding-right: 20em;
    }
}

#content #toc {
    border-style: solid;
    border-width: 1px;
    border-color: #e0e0dc;
    margin-bottom: 1.25em;
    padding: 1.25em;
    background: #f8f8f7;
    -webkit-border-radius: 4px;
    border-radius: 4px;
}

#content #toc > :first-child {
    margin-top: 0;
}

#content #toc > :last-child {
    margin-bottom: 0;
}

#footer {
    max-width: 100%;
    background-color: rgba(0, 0, 0, 0.8);
    padding: 1.25em;
}

#footer-text {
    color: rgba(255, 255, 255, 0.8);
    line-height: 1.44;
}

.sect1 {
    padding-bottom: 0.625em;
}

@media only screen and (min-width: 768px) {
    .sect1 {
        padding-bottom: 1.25em;
    }
}

.sect1 + .sect1 {
    border-top: 1px solid #efefed;
}

#content h1 > a.anchor, h2 > a.anchor, h3 > a.anchor, #toctitle > a.anchor, .sidebarblock > .content > .title > a.anchor, h4 > a.anchor, h5 > a.anchor, h6 > a.anchor {
    position: absolute;
    z-index: 1001;
    width: 1.5ex;
    margin-left: -1.5ex;
    display: block;
    text-decoration: none !important;
    visibility: hidden;
    text-align: center;
    font-weight: normal;
}

#content h1 > a.anchor:before, h2 > a.anchor:before, h3 > a.anchor:before, #toctitle > a.anchor:before, .sidebarblock > .content > .title > a.anchor:before, h4 > a.anchor:before, h5 > a.anchor:before, h6 > a.anchor:before {
    content: "\00A7";
    font-size: 0.85em;
    display: block;
    padding-top: 0.1em;
}

#content h1:hover > a.anchor, #content h1 > a.anchor:hover, h2:hover > a.anchor, h2 > a.anchor:hover, h3:hover > a.anchor, #toctitle:hover > a.anchor, .sidebarblock > .content > .title:hover > a.anchor, h3 > a.anchor:hover, #toctitle > a.anchor:hover, .sidebarblock > .content > .title > a.anchor:hover, h4:hover > a.anchor, h4 > a.anchor:hover, h5:hover > a.anchor, h5 > a.anchor:hover, h6:hover > a.anchor, h6 > a.anchor:hover {
    visibility: visible;
}

#content h1 > a.link, h2 > a.link, h3 > a.link, #toctitle > a.link, .sidebarblock > .content > .title > a.link, h4 > a.link, h5 > a.link, h6 > a.link {
    color: #ba3925;
    text-decoration: none;
}

#content h1 > a.link:hover, h2 > a.link:hover, h3 > a.link:hover, #toctitle > a.link:hover, .sidebarblock > .content > .title > a.link:hover, h4 > a.link:hover, h5 > a.link:hover, h6 > a.link:hover {
    color: #a53221;
}

.audioblock, .imageblock, .literalblock, .listingblock, .stemblock, .videoblock {
    margin-bottom: 1.25em;
}

.admonitionblock td.content > .title, .audioblock > .title, .exampleblock > .title, .imageblock > .title, .listingblock > .title, .literalblock > .title, .stemblock > .title, .openblock > .title, .paragraph > .title, .quoteblock > .title, table.tableblock > .title, .verseblock > .title, .videoblock > .title, .dlist > .title, .olist > .title, .ulist > .title, .qlist > .title, .hdlist > .title {
    text-rendering: optimizeLegibility;
    text-align: left;
    font-family: "Noto Serif", "DejaVu Serif", serif;
    font-size: 1rem;
    font-style: italic;
}

table.tableblock > caption.title {
    white-space: nowrap;
    overflow: visible;
    max-width: 0;
}

.paragraph.lead > p, #preamble > .sectionbody > .paragraph:first-of-type p {
    color: rgba(0, 0, 0, 0.85);
}

table.tableblock #preamble > .sectionbody > .paragraph:first-of-type p {
    font-size: inherit;
}

.admonitionblock > table {
    border-collapse: separate;
    border: 0;
    background: none;
    width: 100%;
}

.admonitionblock > table td.icon {
    text-align: center;
    width: 80px;
}

.admonitionblock > table td.icon img {
    max-width: none;
}

.admonitionblock > table td.icon .title {
    font-weight: bold;
    font-family: "Open Sans", "DejaVu Sans", sans-serif;
    text-transform: uppercase;
}

.admonitionblock > table td.content {
    padding-left: 1.125em;
    padding-right: 1.25em;
    border-left: 1px solid #ddddd8;
    color: rgba(0, 0, 0, 0.6);
}

.admonitionblock > table td.content > :last-child > :last-child {
    margin-bottom: 0;
}

.exampleblock > .content {
    border-style: solid;
    border-width: 1px;
    border-color: #e6e6e6;
    margin-bottom: 1.25em;
    padding: 1.25em;
    background: white;
    -webkit-border-radius: 4px;
    border-radius: 4px;
}

.exampleblock > .content > :first-child {
    margin-top: 0;
}

.exampleblock > .content > :last-child {
    margin-bottom: 0;
}

.sidebarblock {
    border-style: solid;
    border-width: 1px;
    border-color: #e0e0dc;
    margin-bottom: 1.25em;
    padding: 1.25em;
    background: #f8f8f7;
    -webkit-border-radius: 4px;
    border-radius: 4px;
}

.sidebarblock > :first-child {
    margin-top: 0;
}

.sidebarblock > :last-child {
    margin-bottom: 0;
}

.sidebarblock > .content > .title {
    color: #7a2518;
    margin-top: 0;
    text-align: center;
}

.exampleblock > .content > :last-child > :last-child, .exampleblock > .content .olist > ol > li:last-child > :last-child, .exampleblock > .content .ulist > ul > li:last-child > :last-child, .exampleblock > .content .qlist > ol > li:last-child > :last-child, .sidebarblock > .content > :last-child > :last-child, .sidebarblock > .content .olist > ol > li:last-child > :last-child, .sidebarblock > .content .ulist > ul > li:last-child > :last-child, .sidebarblock > .content .qlist > ol > li:last-child > :last-child {
    margin-bottom: 0;
}

.literalblock pre, .listingblock pre:not(.highlight), .listingblock pre[class="highlight"], .listingblock pre[class^="highlight "], .listingblock pre.CodeRay, .listingblock pre.prettyprint {
    background: #f7f7f8;
}

.sidebarblock .literalblock pre, .sidebarblock .listingblock pre:not(.highlight), .sidebarblock .listingblock pre[class="highlight"], .sidebarblock .listingblock pre[class^="highlight "], .sidebarblock .listingblock pre.CodeRay, .sidebarblock .listingblock pre.prettyprint {
    background: #f2f1f1;
}

.literalblock pre, .literalblock pre[class], .listingblock pre, .listingblock pre[class] {
    -webkit-border-radius: 4px;
    border-radius: 4px;
    word-wrap: break-word;
    padding: 1em;
    font-size: 0.8125em;
}

.literalblock pre.nowrap, .literalblock pre[class].nowrap, .listingblock pre.nowrap, .listingblock pre[class].nowrap {
    overflow-x: auto;
    white-space: pre;
    word-wrap: normal;
}

@media only screen and (min-width: 768px) {
    .literalblock pre, .literalblock pre[class], .listingblock pre, .listingblock pre[class] {
        font-size: 0.90625em;
    }
}

@media only screen and (min-width: 1280px) {
    .literalblock pre, .literalblock pre[class], .listingblock pre, .listingblock pre[class] {
        font-size: 1em;
    }
}

.literalblock.output pre {
    color: #f7f7f8;
    background-color: rgba(0, 0, 0, 0.9);
}

.listingblock pre.highlightjs {
    padding: 0;
}

.listingblock pre.highlightjs > code {
    padding: 1em;
    -webkit-border-radius: 4px;
    border-radius: 4px;
}

.listingblock pre.prettyprint {
    border-width: 0;
}

.listingblock > .content {
    position: relative;
}

.listingblock code[data-lang]:before {
    display: none;
    content: attr(data-lang);
    position: absolute;
    font-size: 0.75em;
    top: 0.425rem;
    right: 0.5rem;
    line-height: 1;
    text-transform: uppercase;
    color: #999;
}

.listingblock:hover code[data-lang]:before {
    display: block;
}

.listingblock.terminal pre .command:before {
    content: attr(data-prompt);
    padding-right: 0.5em;
    color: #999;
}

.listingblock.terminal pre .command:not([data-prompt]):before {
    content: "$";
}

table.pyhltable {
    border-collapse: separate;
    border: 0;
    margin-bottom: 0;
    background: none;
}

table.pyhltable td {
    vertical-align: top;
    padding-top: 0;
    padding-bottom: 0;
}

table.pyhltable td.code {
    padding-left: .75em;
    padding-right: 0;
}

pre.pygments .lineno, table.pyhltable td:not(.code) {
    color: #999;
    padding-left: 0;
    padding-right: .5em;
    border-right: 1px solid #ddddd8;
}

pre.pygments .lineno {
    display: inline-block;
    margin-right: .25em;
}

table.pyhltable .linenodiv {
    background: none !important;
    padding-right: 0 !important;
}

.quoteblock {
    margin: 0 1em 1.25em 1.5em;
    display: table;
}

.quoteblock > .title {
    margin-left: -1.5em;
    margin-bottom: 0.75em;
}

.quoteblock blockquote, .quoteblock blockquote p {
    color: rgba(0, 0, 0, 0.85);
    font-size: 1.15rem;
    line-height: 1.75;
    word-spacing: 0.1em;
    letter-spacing: 0;
    font-style: italic;
    text-align: justify;
}

.quoteblock blockquote {
    margin: 0;
    padding: 0;
    border: 0;
}

.quoteblock blockquote:before {
    content: "\201c";
    float: left;
    font-size: 2.75em;
    font-weight: bold;
    line-height: 0.6em;
    margin-left: -0.6em;
    color: #7a2518;
    text-shadow: 0 1px 2px rgba(0, 0, 0, 0.1);
}

.quoteblock blockquote > .paragraph:last-child p {
    margin-bottom: 0;
}

.quoteblock .attribution {
    margin-top: 0.5em;
    margin-right: 0.5ex;
    text-align: right;
}

.quoteblock .quoteblock {
    margin-left: 0;
    margin-right: 0;
    padding: 0.5em 0;
    border-left: 3px solid rgba(0, 0, 0, 0.6);
}

.quoteblock .quoteblock blockquote {
    padding: 0 0 0 0.75em;
}

.quoteblock .quoteblock blockquote:before {
    display: none;
}

.verseblock {
    margin: 0 1em 1.25em 1em;
}

.verseblock pre {
    font-family: "Open Sans", "DejaVu Sans", sans;
    font-size: 1.15rem;
    color: rgba(0, 0, 0, 0.85);
    font-weight: 300;
    text-rendering: optimizeLegibility;
}

.verseblock pre strong {
    font-weight: 400;
}

.verseblock .attribution {
    margin-top: 1.25rem;
    margin-left: 0.5ex;
}

.quoteblock .attribution, .verseblock .attribution {
    font-size: 0.9375em;
    line-height: 1.45;
    font-style: italic;
}

.quoteblock .attribution br, .verseblock .attribution br {
    display: none;
}

.quoteblock .attribution cite, .verseblock .attribution cite {
    display: block;
    letter-spacing: -0.05em;
    color: rgba(0, 0, 0, 0.6);
}

.quoteblock.abstract {
    margin: 0 0 1.25em 0;
    display: block;
}

.quoteblock.abstract blockquote, .quoteblock.abstract blockquote p {
    text-align: left;
    word-spacing: 0;
}

.quoteblock.abstract blockquote:before, .quoteblock.abstract blockquote p:first-of-type:before {
    display: none;
}

table.tableblock {
    max-width: 100%;
    border-collapse: separate;
}

table.tableblock td > .paragraph:last-child p > p:last-child, table.tableblock th > p:last-child, table.tableblock td > p:last-child {
    margin-bottom: 0;
}

table.spread {
    width: 100%;
}

table.tableblock, th.tableblock, td.tableblock {
    border: 0 solid #dedede;
}

table.grid-all th.tableblock, table.grid-all td.tableblock {
    border-width: 0 1px 1px 0;
}

table.grid-all tfoot > tr > th.tableblock, table.grid-all tfoot > tr > td.tableblock {
    border-width: 1px 1px 0 0;
}

table.grid-cols th.tableblock, table.grid-cols td.tableblock {
    border-width: 0 1px 0 0;
}

table.grid-all * > tr > .tableblock:last-child, table.grid-cols * > tr > .tableblock:last-child {
    border-right-width: 0;
}

table.grid-rows th.tableblock, table.grid-rows td.tableblock {
    border-width: 0 0 1px 0;
}

table.grid-all tbody > tr:last-child > th.tableblock, table.grid-all tbody > tr:last-child > td.tableblock, table.grid-all thead:last-child > tr > th.tableblock, table.grid-rows tbody > tr:last-child > th.tableblock, table.grid-rows tbody > tr:last-child > td.tableblock, table.grid-rows thead:last-child > tr > th.tableblock {
    border-bottom-width: 0;
}

table.grid-rows tfoot > tr > th.tableblock, table.grid-rows tfoot > tr > td.tableblock {
    border-width: 1px 0 0 0;
}

table.frame-all {
    border-width: 1px;
}

table.frame-sides {
    border-width: 0 1px;
}

table.frame-topbot {
    border-width: 1px 0;
}

th.halign-left, td.halign-left {
    text-align: left;
}

th.halign-right, td.halign-right {
    text-align: right;
}

th.halign-center, td.halign-center {
    text-align: center;
}

th.valign-top, td.valign-top {
    vertical-align: top;
}

th.valign-bottom, td.valign-bottom {
    vertical-align: bottom;
}

th.valign-middle, td.valign-middle {
    vertical-align: middle;
}

table thead th, table tfoot th {
    font-weight: bold;
}

tbody tr th {
    display: table-cell;
    line-height: 1.6;
    background: #f7f8f7;
}

tbody tr th, tbody tr th p, tfoot tr th, tfoot tr th p {
    color: rgba(0, 0, 0, 0.8);
    font-weight: bold;
}

p.tableblock > code:only-child {
    background: none;
    padding: 0;
}

p.tableblock {
    font-size: 1em;
}

td > div.verse {
    white-space: pre;
}

ol {
    margin-left: 1.75em;
}

ul li ol {
    margin-left: 1.5em;
}

dl dd {
    margin-left: 1.125em;
}

dl dd:last-child, dl dd:last-child > :last-child {
    margin-bottom: 0;
}

ol > li p, ul > li p, ul dd, ol dd, .olist .olist, .ulist .ulist, .ulist .olist, .olist .ulist {
    margin-bottom: 0.625em;
}

ul.unstyled, ol.unnumbered, ul.checklist, ul.none {
    list-style-type: none;
}

ul.unstyled, ol.unnumbered, ul.checklist {
    margin-left: 0.625em;
}

ul.checklist li > p:first-child > .fa-check-square-o:first-child, ul.checklist li > p:first-child > input[type="checkbox"]:first-child {
    margin-right: 0.25em;
}

ul.checklist li > p:first-child > input[type="checkbox"]:first-child {
    position: relative;
    top: 1px;
}

ul.inline {
    margin: 0 auto 0.625em auto;
    margin-left: -1.375em;
    margin-right: 0;
    padding: 0;
    list-style: none;
    overflow: hidden;
}

ul.inline > li {
    list-style: none;
    float: left;
    margin-left: 1.375em;
    display: block;
}

ul.inline > li > * {
    display: block;
}

.unstyled dl dt {
    font-weight: normal;
    font-style: normal;
}

ol.arabic {
    list-style-type: decimal;
}

ol.decimal {
    list-style-type: decimal-leading-zero;
}

ol.loweralpha {
    list-style-type: lower-alpha;
}

ol.upperalpha {
    list-style-type: upper-alpha;
}

ol.lowerroman {
    list-style-type: lower-roman;
}

ol.upperroman {
    list-style-type: upper-roman;
}

ol.lowergreek {
    list-style-type: lower-greek;
}

.hdlist > table, .colist > table {
    border: 0;
    background: none;
}

.hdlist > table > tbody > tr, .colist > table > tbody > tr {
    background: none;
}

td.hdlist1 {
    padding-right: .75em;
    font-weight: bold;
}

td.hdlist1, td.hdlist2 {
    vertical-align: top;
}

.literalblock + .colist, .listingblock + .colist {
    margin-top: -0.5em;
}

.colist > table tr > td:first-of-type {
    padding: 0 0.75em;
    line-height: 1;
}

.colist > table tr > td:last-of-type {
    padding: 0.25em 0;
}

.thumb, .th {
    line-height: 0;
    display: inline-block;
    border: solid 4px white;
    -webkit-box-shadow: 0 0 0 1px #dddddd;
    box-shadow: 0 0 0 1px #dddddd;
}

.imageblock.left, .imageblock[style*="float: left"] {
    margin: 0.25em 0.625em 1.25em 0;
}

.imageblock.right, .imageblock[style*="float: right"] {
    margin: 0.25em 0 1.25em 0.625em;
}

.imageblock > .title {
    margin-bottom: 0;
}

.imageblock.thumb, .imageblock.th {
    border-width: 6px;
}

.imageblock.thumb > .title, .imageblock.th > .title {
    padding: 0 0.125em;
}

.image.left, .image.right {
    margin-top: 0.25em;
    margin-bottom: 0.25em;
    display: inline-block;
    line-height: 0;
}

.image.left {
    margin-right: 0.625em;
}

.image.right {
    margin-left: 0.625em;
}

a.image {
    text-decoration: none;
}

span.footnote, span.footnoteref {
    vertical-align: super;
    font-size: 0.875em;
}

span.footnote a, span.footnoteref a {
    text-decoration: none;
}

span.footnote a:active, span.footnoteref a:active {
    text-decoration: underline;
}

#footnotes {
    padding-top: 0.75em;
    padding-bottom: 0.75em;
    margin-bottom: 0.625em;
}

#footnotes hr {
    width: 20%;
    min-width: 6.25em;
    margin: -.25em 0 .75em 0;
    border-width: 1px 0 0 0;
}

#footnotes .footnote {
    padding: 0 0.375em;
    line-height: 1.3;
    font-size: 0.875em;
    margin-left: 1.2em;
    text-indent: -1.2em;
    margin-bottom: .2em;
}

#footnotes .footnote a:first-of-type {
    font-weight: bold;
    text-decoration: none;
}

#footnotes .footnote:last-of-type {
    margin-bottom: 0;
}

#content #footnotes {
    margin-top: -0.625em;
    margin-bottom: 0;
    padding: 0.75em 0;
}

.gist .file-data > table {
    border: 0;
    background: #fff;
    width: 100%;
    margin-bottom: 0;
}

.gist .file-data > table td.line-data {
    width: 99%;
}

div.unbreakable {
    page-break-inside: avoid;
}

.big {
    font-size: larger;
}

.small {
    font-size: smaller;
}

.underline {
    text-decoration: underline;
}

.overline {
    text-decoration: overline;
}

.line-through {
    text-decoration: line-through;
}

.aqua {
    color: #00bfbf;
}

.aqua-background {
    background-color: #00fafa;
}

.black {
    color: black;
}

.black-background {
    background-color: black;
}

.blue {
    color: #0000bf;
}

.blue-background {
    background-color: #0000fa;
}

.fuchsia {
    color: #bf00bf;
}

.fuchsia-background {
    background-color: #fa00fa;
}

.gray {
    color: #606060;
}

.gray-background {
    background-color: #7d7d7d;
}

.green {
    color: #006000;
}

.green-background {
    background-color: #007d00;
}

.lime {
    color: #00bf00;
}

.lime-background {
    background-color: #00fa00;
}

.maroon {
    color: #600000;
}

.maroon-background {
    background-color: #7d0000;
}

.navy {
    color: #000060;
}

.navy-background {
    background-color: #00007d;
}

.olive {
    color: #606000;
}

.olive-background {
    background-color: #7d7d00;
}

.purple {
    color: #600060;
}

.purple-background {
    background-color: #7d007d;
}

.red {
    color: #bf0000;
}

.red-background {
    background-color: #fa0000;
}

.silver {
    color: #909090;
}

.silver-background {
    background-color: #bcbcbc;
}

.teal {
    color: #006060;
}

.teal-background {
    background-color: #007d7d;
}

.white {
    color: #bfbfbf;
}

.white-background {
    background-color: #fafafa;
}

.yellow {
    color: #bfbf00;
}

.yellow-background {
    background-color: #fafa00;
}

span.icon > .fa {
    cursor: default;
}

.admonitionblock td.icon [class^="fa icon-"] {
    font-size: 2.5em;
    text-shadow: 1px 1px 2px rgba(0, 0, 0, 0.5);
    cursor: default;
}

.admonitionblock td.icon .icon-note:before {
    content: "\f05a";
    color: #19407c;
}

.admonitionblock td.icon .icon-tip:before {
    content: "\f0eb";
    text-shadow: 1px 1px 2px rgba(155, 155, 0, 0.8);
    color: #111;
}

.admonitionblock td.icon .icon-warning:before {
    content: "\f071";
    color: #bf6900;
}

.admonitionblock td.icon .icon-caution:before {
    content: "\f06d";
    color: #bf3400;
}

.admonitionblock td.icon .icon-important:before {
    content: "\f06a";
    color: #bf0000;
}

.conum[data-value] {
    display: inline-block;
    color: #fff !important;
    background-color: rgba(0, 0, 0, 0.8);
    -webkit-border-radius: 100px;
    border-radius: 100px;
    text-align: center;
    font-size: 0.75em;
    width: 1.67em;
    height: 1.67em;
    line-height: 1.67em;
    font-family: "Open Sans", "DejaVu Sans", sans-serif;
    font-style: normal;
    font-weight: bold;
}

.conum[data-value] * {
    color: #fff !important;
}

.conum[data-value] + b {
    display: none;
}

.conum[data-value]:after {
    content: attr(data-value);
}

pre .conum[data-value] {
    position: relative;
    top: -0.125em;
}

b.conum * {
    color: inherit !important;
}

.conum:not([data-value]):empty {
    display: none;
}

h1, h2 {
    letter-spacing: -0.01em;
}

dt, th.tableblock, td.content {
    text-rendering: optimizeLegibility;
}

p, td.content {
    letter-spacing: -0.01em;
}

p strong, td.content strong {
    letter-spacing: -0.005em;
}

p, blockquote, dt, td.content {
    font-size: 1.0625rem;
}

p {
    margin-bottom: 1.25rem;
}

.sidebarblock p, .sidebarblock dt, .sidebarblock td.content, p.tableblock {
    font-size: 1em;
}

.exampleblock > .content {
    background-color: #fffef7;
    border-color: #e0e0dc;
    -webkit-box-shadow: 0 1px 4px #e0e0dc;
    box-shadow: 0 1px 4px #e0e0dc;
}

.print-only {
    display: none !important;
}

@media print {
    @page {
        margin: 1.25cm 0.75cm;
    }

    * {
        -webkit-box-shadow: none !important;
        box-shadow: none !important;
        text-shadow: none !important;
    }

    a {
        color: inherit !important;
        text-decoration: underline !important;
    }

    a.bare, a[href^="#"], a[href^="mailto:"] {
        text-decoration: none !important;
    }

    a[href^="http:"]:not(.bare):after, a[href^="https:"]:not(.bare):after, a[href^="mailto:"]:not(.bare):after {
        content: "(" attr(href) ")";
        display: inline-block;
        font-size: 0.875em;
        padding-left: 0.25em;
    }

    abbr[title]:after {
        content: " (" attr(title) ")";
    }

    pre, blockquote, tr, img {
        page-break-inside: avoid;
    }

    thead {
        display: table-header-group;
    }

    img {
        max-width: 100% !important;
    }

    p, blockquote, dt, td.content {
        font-size: 1em;
        orphans: 3;
        widows: 3;
    }

    h2, h3, #toctitle, .sidebarblock > .content > .title, #toctitle, .sidebarblock > .content > .title {
        page-break-after: avoid;
    }

    #toc, .sidebarblock, .exampleblock > .content {
        background: none !important;
    }

    #toc {
        border-bottom: 1px solid #ddddd8 !important;
        padding-bottom: 0 !important;
    }

    .sect1 {
        padding-bottom: 0 !important;
    }

    .sect1 + .sect1 {
        border: 0 !important;
    }

    #header > h1:first-child {
        margin-top: 1.25rem;
    }

    body.book #header {
        text-align: center;
    }

    body.book #header > h1:first-child {
        border: 0 !important;
        margin: 2.5em 0 1em 0;
    }

    body.book #header .details {
        border: 0 !important;
        display: block;
        padding: 0 !important;
    }

    body.book #header .details span:first-child {
        margin-left: 0 !important;
    }

    body.book #header .details br {
        display: block;
    }

    body.book #header .details br + span:before {
        content: none !important;
    }

    body.book #toc {
        border: 0 !important;
        text-align: left !important;
        padding: 0 !important;
        margin: 0 !important;
    }

    body.book #toc, body.book #preamble, body.book h1.sect0, body.book .sect1 > h2 {
        page-break-before: always;
    }

    .listingblock code[data-lang]:before {
        display: block;
    }

    #footer {
        background: none !important;
        padding: 0 0.9375em;
    }

    #footer-text {
        color: rgba(0, 0, 0, 0.6) !important;
        font-size: 0.9em;
    }

    .hide-on-print {
        display: none !important;
    }

    .print-only {
        display: block !important;
    }

    .hide-for-print {
        display: none !important;
    }

    .show-for-print {
        display: inherit !important;
    }
}
</style>
</head>
<body class="article">
<div id="header">
<h1>BeetlSQL User Guide</h1>
<div id="toc" class="toc">
<div id="toctitle">Table of Contents</div>
<ul class="sectlevel1">
<li><a href="#truebeetlsql">1. beetlsql 特点</a></li>
<li><a href="#true5">2. 5 分钟例子</a>
<ul class="sectlevel2">
<li><a href="#true-">2.1. 安装</a></li>
<li><a href="#true--2">2.2. 准备工作</a></li>
<li><a href="#true--3">2.3. 代码例子</a></li>
<li><a href="#truesql">2.4. SQL文件例子</a></li>
<li><a href="#true-sql">2.5. 代码&amp;sql生成</a></li>
</ul>
</li>
<li><a href="#truebeetlsql-2">3. BeetlSQL 说明</a>
<ul class="sectlevel2">
<li><a href="#true-sqlmanager">3.1. 获得SQLManager</a></li>
<li><a href="#true-api">3.2. 查询API</a></li>
<li><a href="#true-api-2">3.3. 翻页查询API</a></li>
<li><a href="#true-api-3">3.4. 更新API</a></li>
<li><a href="#true-sql-4">3.5. 直接执行SQL</a></li>
<li><a href="#true--4">3.6. 其他</a></li>
</ul>
</li>
<li><a href="#true--6">4. 命名转化，表和列名映射</a></li>
<li><a href="#true--7">5. 复合主键</a></li>
<li><a href="#true-mapper">6. 使用Mapper</a></li>
<li><a href="#truebeetlsql-annotation">7. BeetlSQL Annotation</a>
<ul class="sectlevel2">
<li><a href="#true-autoid-assignid-seqid">7.1. @AutoID 和 @AssignID ，@SeqID</a></li>
<li><a href="#true-tail">7.2. @Tail</a></li>
<li><a href="#true-columnignore">7.3. @ColumnIgnore</a></li>
<li><a href="#true-enummapping">7.4. @EnumMapping</a></li>
<li><a href="#true-table">7.5. @Table</a></li>
<li><a href="#true-tabletemplate">7.6. @TableTemplate</a></li>
<li><a href="#truemapper">7.7. Mapper相关注解</a></li>
</ul>
</li>
<li><a href="#truebeetlsql-3">8. BeetlSQL 数据模型</a></li>
<li><a href="#truemarkdown">9. Markdown方式管理</a></li>
<li><a href="#truesql-2">10. SQL 注释</a></li>
<li><a href="#true-beetlsql">11. (重要) 配置beetlsql</a>
<ul class="sectlevel2">
<li><a href="#true--8">11.1. 开发模式和产品模式</a></li>
<li><a href="#true--9">11.2. 模板字符集</a></li>
<li><a href="#true-0-1">11.3. 翻页起始参数是0还是1</a></li>
<li><a href="#true--10">11.4. 自定义方法和标签函数</a></li>
</ul>
</li>
<li><a href="#truesql-beetl">12. SQL 模板基于Beetl实现</a></li>
<li><a href="#truebeetl">13. Beetl 入门</a>
<ul class="sectlevel2">
<li><a href="#true--11">13.1. 定界符号</a></li>
<li><a href="#true--12">13.2. 变量</a></li>
<li><a href="#true--13">13.3. 算数表达式</a></li>
<li><a href="#true--14">13.4. 逻辑表达式</a></li>
<li><a href="#true--15">13.5. 控制语句</a></li>
<li><a href="#true--16">13.6. 访问变量属性</a></li>
<li><a href="#true--17">13.7. 判断对象非空空</a></li>
<li><a href="#true--18">13.8. 调用方法</a></li>
<li><a href="#true--19">13.9. 自定义方法</a></li>
<li><a href="#true--20">13.10. 内置方法</a></li>
<li><a href="#true--21">13.11. 标签功能</a></li>
</ul>
</li>
<li><a href="#truedebug">14. Debug功能</a></li>
<li><a href="#trueinterceptor">15. Interceptor功能</a></li>
<li><a href="#true--22">16. 内置支持主从数据库</a></li>
<li><a href="#true--23">17. 可以支持更复杂的分库分表逻辑</a></li>
<li><a href="#true--24">18. 跨数据库平台</a></li>
<li><a href="#true--25">19. 代码生成</a>
<ul class="sectlevel2">
<li><a href="#true-pojo-md">19.1. 生成pojo 和 md文件</a></li>
<li><a href="#true--26">19.2. 生成更多的代码</a></li>
</ul>
</li>
<li><a href="#true-sqlresult">20. 直接使用SQLResult</a></li>
<li><a href="#truehibernate-mybatis-mysql">21. Hibernate,MyBatis,MySQL 对比</a></li>
<li><a href="#trueorm">22. ORM</a></li>
<li><a href="#true-demo">23. 集成和Demo</a>
<ul class="sectlevel2">
<li><a href="#truespring-demo">23.1. Spring集成和Demo</a></li>
<li><a href="#truejfinal-demo">23.2. JFinal集成和Demo</a></li>
</ul>
</li>
</ul>
</div>
</div>
<div id="content">
<div id="preamble">
<div class="sectionbody">
<div class="sidebarblock">
<div class="content">
<div class="title">支持</div>
<div class="ulist">
<ul>
<li>
<p>作者: 闲大赋,Gavin.King,Sue,Zhoupan,woate</p>
</li>
<li>
<p>社区 <a href="http://ibeetl.com" class="bare">http://ibeetl.com</a></p>
</li>
<li>
<p>qq群 219324263</p>
</li>
<li>
<p>当前版本 2.6.4 (208K), 另外还需要beetl(<a href="http://git.oschina.net/xiandafu/beetl2.0/attach_files" class="bare">http://git.oschina.net/xiandafu/beetl2.0/attach_files</a>) 包</p>
</li>
</ul>
</div>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="truebeetlsql"><a class="anchor" href="#truebeetlsql"></a>1. beetlsql 特点</h2>
<div class="sectionbody">
<div class="paragraph">
<p>BeetSql是一个全功能DAO工具， 同时具有Hibernate 优点 &amp; Mybatis优点功能，适用于承认以SQL为中心，同时又需求工具能自动能生成大量常用的SQL的应用。</p>
</div>
<div class="ulist">
<ul>
<li>
<p>开发效率</p>
<div class="ulist">
<ul>
<li>
<p>无需注解，自动使用大量内置SQL，轻易完成增删改查功能，节省50%的开发工作量</p>
</li>
<li>
<p>数据模型支持Pojo，也支持Map/List这种快速模型，也支持混合模型</p>
</li>
<li>
<p>SQL 模板基于Beetl实现，更容易写和调试，以及扩展</p>
</li>
<li>
<p>可以针对单个表(或者视图）代码生成pojo类和sql模版，甚至是整个数据库。能减少代码编写工作量</p>
</li>
</ul>
</div>
</li>
<li>
<p>维护性</p>
<div class="ulist">
<ul>
<li>
<p>SQL 以更简洁的方式，Markdown方式集中管理，同时方便程序开发和数据库SQL调试。</p>
</li>
<li>
<p>可以自动将sql文件映射为dao接口类</p>
</li>
<li>
<p>简单支持支持一对一，一对多，多对多关系映射而不引入复杂的OR Mapping概念和技术。</p>
</li>
<li>
<p>具备Interceptor功能，可以调试，性能诊断SQL，以及扩展其他功能</p>
</li>
</ul>
</div>
</li>
<li>
<p>其他</p>
<div class="ulist">
<ul>
<li>
<p>内置支持主从数据库支持的开源工具</p>
</li>
<li>
<p>支持跨数据库平台，开发者所需工作减少到最小，目前跨数据库支持mysql,postgres,oracle,sqlserver,h2,sqllite.</p>
</li>
</ul>
</div>
</li>
</ul>
</div>
</div>
</div>
<div class="sect1">
<h2 id="true5"><a class="anchor" href="#true5"></a>2. 5 分钟例子</h2>
<div class="sectionbody">
<div class="sect2">
<h3 id="true-"><a class="anchor" href="#true-"></a>2.1. 安装</h3>
<div class="paragraph">
<p>maven 方式:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-xml" data-lang="xml">&lt;dependency&gt;
	&lt;groupId&gt;com.ibeetl&lt;/groupId&gt;
	&lt;artifactId&gt;beetl&lt;/artifactId&gt;
	&lt;version&gt;2.6.4&lt;/version&gt;
&lt;/dependency&gt;</code></pre>
</div>
</div>
<div class="paragraph">
<p>或者依次下载beetlsql，beetl 包放到classpath里</p>
</div>
</div>
<div class="sect2">
<h3 id="true--2"><a class="anchor" href="#true--2"></a>2.2. 准备工作</h3>
<div class="paragraph">
<p>为了快速尝试BeetlSQL，需要准备一个Mysql数据库或者其他任何beetlsql支持的数据库，然后执行如下sql脚本</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">CREATE TABLE `user` (
	  `id` int(11) NOT NULL AUTO_INCREMENT,
	  `name` varchar(64) DEFAULT NULL,
	  `age` int(4) DEFAULT NULL,
	  `userName` varchar(64) DEFAULT NULL COMMENT '用户名称',
	  `roleId` int(11) DEFAULT NULL COMMENT '用户角色',
	  `date` datetime NULL DEFAULT NULL,
	  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;</code></pre>
</div>
</div>
<div class="paragraph">
<p>编写一个Pojo类，与数据库表对应（或者可以通过SQLManager的gen方法生成此类，参考一下节）</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">import java.math.*;
import java.util.Date;


/*
*
* gen by beetlsql 2016-01-06
*/
public class User  {
	private Integer id ;
	private Integer age ;
	//用户角色
	private Integer roleId ;
	private String name ;
	//用户名称
	private String userName ;
	private Date date ;

}</code></pre>
</div>
</div>
</div>
<div class="sect2">
<h3 id="true--3"><a class="anchor" href="#true--3"></a>2.3. 代码例子</h3>
<div class="paragraph">
<p>写一个java的Main方法，内容如下</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">ConnectionSource source = ConnectionSourceHelper.getSimple(driver, url, "", userName, password);
DBStyle mysql = new MySqlStyle();
// sql语句放在classpagth的/sql 目录下
SQLLoader loader = new ClasspathLoader("/sql");
// 数据库命名跟java命名一样，所以采用DefaultNameConversion，还有一个是UnderlinedNameConversion，下划线风格的
所以采用DefaultNameConversion nc = new  所以采用DefaultNameConversion();
// 最后，创建一个SQLManager,DebugInterceptor 不是必须的，但可以通过它查看sql执行情况
SQLManager sqlManager = new SQLManager(mysql,loader,source,nc,new Interceptor[]{new DebugInterceptor()});


//使用内置的生成的sql 新增用户，如果需要获取主键，可以传入KeyHolder
User user = new User();
user.setAge(19);
user.setName("xiandafu");
sqlManager.insert(user);

//使用内置sql查询用户
int id = 1;
user = sqlManager.unique(User.class,id);

//模板更新,仅仅根据id更新值不为null的列
User newUser = new User();
newUser.setId(1);
newUser.setAge(20);
sqlManager.updateTemplateById(newUser);

//模板查询
User query = new User();
query.setName("xiandafu");
List&lt;User&gt; list = sqlManager.template(query);


//使用user.md 文件里的select语句，参考下一节。
User query2 = new User();
query.setName("xiandafu");
List&lt;User&gt; list2 = sqlManager.select("user.select",User.class,query2)</code></pre>
</div>
</div>
</div>
<div class="sect2">
<h3 id="truesql"><a class="anchor" href="#truesql"></a>2.4. SQL文件例子</h3>
<div class="paragraph">
<p>通常一个项目还是有少量复杂sql，可能只有5，6行，也可能有上百行，放在单独的sql文件里更容易编写和维护，为了能执行上例的user.select,需要在classpath里建立一个sql目录（ClasspathLoader 配置成sql目录，参考上一节ClasspathLoader初始化的代码）以及下面的user.md 文件，内容如下</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">select
===
select * from user where 1=1
@if(!isEmpty(age)){
and age = #age#
@}
@if(!isEmpty(name)){
and name = #name#
@}</code></pre>
</div>
</div>
<div class="paragraph">
<p>关于如何写sql模板，会稍后章节说明，如下是一些简单说明。</p>
</div>
<div class="ulist">
<ul>
<li>
<p>采用md格式，===上面是sql语句在本文件里的唯一标示，下面则是sql语句。</p>
</li>
<li>
<p>@ 和回车符号是定界符号，可以在里面写beetl语句。</p>
</li>
<li>
<p>"#" 是站位符号，生成sql语句得时候，将输出？，如果你想输出表达式值，需要用text函数，或者任何以db开头的函数，引擎则认为是直接输出文本。</p>
</li>
<li>
<p>isEmpty是beetl的一个函数，用来判断变量是否为空或者是否不存在.</p>
</li>
<li>
<p>文件名约定为类名，首字母小写。</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>sql模板采用beetl原因是因为beetl 语法类似js，且对模板渲染做了特定优化，相比于mybatis，更加容易掌握和功能强大，可读性更好，也容易在java和数据库之间迁移sql语句</p>
</div>
</div>
<div class="sect2">
<h3 id="true-sql"><a class="anchor" href="#true-sql"></a>2.5. 代码&amp;sql生成</h3>
<div class="paragraph">
<p>User类并非需要自己写，好的实践是可以在项目中专门写个类用来辅助生成pojo和sql片段，代码如下</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">public static void main(String[] args){
	SqlManager sqlManager  = ...... //同上面的例子
	sqlManager.genPojoCodeToConsole("user");
	sqlManager.genSQLTemplateToConsole("user");
}</code></pre>
</div>
</div>
<div class="literalblock">
<div class="content">
<pre>注意:我经常在我的项目里写一个这样的辅助类，用来根据表或者视图生成各种代码和sql片段，以快速开发.</pre>
</div>
</div>
<div class="paragraph">
<p>genPojoCodeToConsole 方法可以根据数据库表生成相应的Pojo代码，输出到控制台，开发者可以根据这些代码创建相应的类，如上例子，控制台将输出</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">package com.test;
import java.math.*;
import java.util.Date;
import java.sql.Timestamp;

/*
*
* gen by beetlsql 2016-01-06
*/
public class user  {
	private Integer id ;
	private Integer age ;
	//用户角色
	private Integer roleId ;
	private String name ;
	//用户名称
	private String userName ;
	private Date date ;

}</code></pre>
</div>
</div>
<div class="paragraph">
<p>上述生成的代码有些瑕疵，比如包名总是com.test，类名是小写开头（因为用了DefaultNameConversion)，你需要修改成你要的包名和正常的类名，pojo类也没有生成getter，setter方法，你需要用ide自带的工具再次生成一下。</p>
</div>
<div class="sidebarblock">
<div class="content">
<div class="title">注意</div>
<div class="literalblock">
<div class="content">
<pre>生成属性的时候，id总是在前面，后面依次是类型为Integer的类型，最后面是日期类型，剩下的按照字母排序放到中间。</pre>
</div>
</div>
</div>
</div>
<div class="paragraph">
<p>一旦有了User 类，如果你需要些sql语句，那么genSQLTemplateToConsole 将是个很好的辅助方法，可以输出一系列sql语句片段，你同样可以赋值粘贴到代码或者sql模板文件里（user.md),如上例所述，当调用genSQLTemplateToConsole的时候，生成如下</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">sample
===
* 注释

	select #use("cols")# from user where #use("condition")#

cols
===

	id,name,age,userName,roleId,date

updateSample
===

	`id`=#id#,`name`=#name#,`age`=#age#,`userName`=#userName#,`roleId`=#roleId#,`date`=#date#

condition
===

	1 = 1
	@if(!isEmpty(name)){
	 and `name`=#name#
	@}
	@if(!isEmpty(age)){
	 and `age`=#age#
	@}</code></pre>
</div>
</div>
<div class="paragraph">
<p>beetlsql生成了用于查询，更新，条件的sql片段和一个简单例子。你可以按照你的需要copy到sql模板文件里.实际上，如果你熟悉gen方法，你可以直接gen代码和sql到你的工程里，甚至是整个数据库都可以调用genAll来一次生成</p>
</div>
<div class="sidebarblock">
<div class="content">
<div class="title">注意</div>
<div class="literalblock">
<div class="content">
<pre>sql 片段的生成顺序按照数据库表定义的顺序显示</pre>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="truebeetlsql-2"><a class="anchor" href="#truebeetlsql-2"></a>3. BeetlSQL 说明</h2>
<div class="sectionbody">
<div class="sect2">
<h3 id="true-sqlmanager"><a class="anchor" href="#true-sqlmanager"></a>3.1. 获得SQLManager</h3>
<div class="paragraph">
<p>SQLManager 是系统的核心，他提供了所有的dao方法。获得SQLManager，可以直接构造SQLManager.并通过过单例获取如：</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">ConnectionSource source = ConnectionSourceHelper.getSimple(driver, url, "", userName, password);
DBStyle mysql = new MySqlStyle();
// sql语句放在classpagth的/sql 目录下
SQLLoader loader = new ClasspathLoader("/sql");
// 数据库命名跟java命名一样，所以采用DefaultNameConversion，还有一个是UnderlinedNameConversion，下划线风格的
UnderlinedNameConversion nc = new  UnderlinedNameConversion();
// 最后，创建一个SQLManager,DebugInterceptor 不是必须的，但可以通过它查看sql执行情况
SQLManager sqlManager = new SQLManager(mysql,loader,source,nc,new Interceptor[]{new DebugInterceptor()});</code></pre>
</div>
</div>
<div class="paragraph">
<p>更常见的是，已经有了DataSource，创建ConnectionSource 可以采用如下代码</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">ConnectionSource source = ConnectionSourceHelper.single(datasource);</code></pre>
</div>
</div>
<div class="paragraph">
<p>如果是主从Datasource</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">ConnectionSource source = ConnectionSourceHelper.getMasterSlave(master,slaves)</code></pre>
</div>
</div>
</div>
<div class="sect2">
<h3 id="true-api"><a class="anchor" href="#true-api"></a>3.2. 查询API</h3>
<div class="sect3">
<h4 id="true-sql-2"><a class="anchor" href="#true-sql-2"></a>3.2.1. 模板类查询（自动生成sql）</h4>
<div class="ulist">
<ul>
<li>
<p>public &lt;T&gt; List&lt;T&gt; all(Class&lt;T&gt; clazz)  查询出所有结果集</p>
</li>
<li>
<p>public &lt;T&gt; List&lt;T&gt; all(Class&lt;T&gt; clazz, int start, int size)  翻页</p>
</li>
<li>
<p>public int allCount(Class&lt;?&gt; clazz) 总数</p>
</li>
<li>
<p>public &lt;T&gt; List&lt;T&gt; template(T t)  根据模板查询，返回所有符合这个模板的数据库
同上，mapper可以提供额外的映射，如处理一对多，一对一</p>
</li>
<li>
<p>public &lt;T&gt; List&lt;T&gt; template(T t,int start,int size) 同上，可以翻页</p>
</li>
<li>
<p>public &lt;T&gt; long templateCount(T t) 获取符合条件的个数</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>翻页的start，系统默认位从1开始，为了兼容各个数据库系统，会自动翻译成数据库习俗，比如start为1，会认为mysql，postgres从0开始（从start－1开始），oralce从1开始（start－0）开始。</p>
</div>
<div class="paragraph">
<p>然而，如果你只用特定数据库，可以按照特定数据库习俗来，比如，你只用mysql，start为0代表起始纪录，需要配置</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-text" data-lang="text">OFFSET_START_ZERO = true</code></pre>
</div>
</div>
<div class="paragraph">
<p>这样，翻页参数start传入0即可。</p>
</div>
<div class="paragraph">
<p>注意:根据模板查询并不包含时间字段，也不包含排序，然而，可以通过在pojo class上使用@TableTemplate() 或者日期字段的getter方法上使用@DateTemplate()来定制，如下:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">@TableTemplate("order by id desc ")
public class User  {
	private Integer id ;
	private Integer age ;
	// ...
	@DateTemplate(accept="minDate,maxDate")
	public Date getDate() {
		return date;
	}
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>这样，模板查询将添加order by id desc ,以及date字段将按照日期范围来查询。 具体参考annotation一章</p>
</div>
</div>
<div class="sect3">
<h4 id="true-sqlid-sql-md"><a class="anchor" href="#true-sqlid-sql-md"></a>3.2.2. 通过sqlid查询,sql语句在md文件里</h4>
<div class="ulist">
<ul>
<li>
<p>public &lt;T&gt; List&lt;T&gt; select(String sqlId, Class&lt;T&gt; clazz, Map&lt;String, Object&gt; paras) 根据sqlid来查询，参数是个map</p>
</li>
<li>
<p>public &lt;T&gt; List&lt;T&gt; select(String sqlId, Class&lt;T&gt; clazz, Object paras) 根据sqlid来查询，参数是个pojo</p>
</li>
<li>
<p>public &lt;T&gt; List&lt;T&gt; select(String sqlId, Class&lt;T&gt; clazz, Map&lt;String, Object&gt; paras, int start, int size)， 增加翻页</p>
</li>
<li>
<p>public &lt;T&gt; List&lt;T&gt; select(String sqlId, Class&lt;T&gt; clazz, Object paras, int start, int size) ，增加翻页</p>
</li>
<li>
<p>public &lt;T&gt; T selectSingle(String id,Object paras, Class&lt;T&gt; target) 根据sqlid查询，输入是Pojo，将对应的唯一值映射成指定的taget对象，如果未找到，则返回空。需要注意的时候，有时候结果集本生是空，这时候建议使用unique</p>
</li>
<li>
<p>public &lt;T&gt; T selectSingle(String id,Map&lt;String, Object&gt; paras, Class&lt;T&gt; target) 根据sqlid查询，输入是Map，将对应的唯一值映射成指定的taget对象，如果未找到，则返回空。需要注意的时候，有时候结果集本生是空，这时候建议使用unique</p>
</li>
<li>
<p>public &lt;T&gt; T selectUnique(String id,Object paras, Class&lt;T&gt; target) 根据sqlid查询，输入是Pojo或者Map，将对应的唯一值映射成指定的taget对象,如果未找到，则抛出异常</p>
</li>
<li>
<p>public &lt;T&gt; T selectUnique(String id,Map&lt;String, Object&gt; paras, Class&lt;T&gt; target) 根据sqlid查询，输入是Pojo或者Map，将对应的唯一值映射成指定的taget对象,如果未找到，则抛出异常</p>
</li>
</ul>
</div>
<div class="sidebarblock">
<div class="content">
<div class="title">注意</div>
<div class="paragraph">
<p>sqlserver 翻页依赖对id的排序，因此，请保证sqlserver表由主键id，否则不能start,size 进行翻页，只能自己写sql语句完成翻页</p>
</div>
</div>
</div>
<div class="ulist">
<ul>
<li>
<p>public Integer  intValue(String id,Object paras) 查询结果映射成Integer，如果找不到，返回null，输入是objct</p>
</li>
<li>
<p>public Integer  intValue(String id,Map paras) 查询结果映射成Integer，如果找不到，返回null，输入是map，其他还有 longValue，bigDecimalValue</p>
</li>
<li>
<p>public &lt;T&gt; T unique(Class&lt;T&gt; clazz,Object pk) 根据主键查询，如果未找到，抛出异常.</p>
</li>
</ul>
</div>
</div>
</div>
<div class="sect2">
<h3 id="true-api-2"><a class="anchor" href="#true-api-2"></a>3.3. 翻页查询API</h3>
<div class="ulist">
<ul>
<li>
<p>public &lt;T&gt; void pageQuery(String sqlId,Class&lt;T&gt; clazz,PageQuery query)</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>BeetlSQL 提供一个PageQUery对象,用于web应用的翻页查询,BeetlSql假定有sqlId 和sqlId$count,俩个sqlId,并用这来个来翻页和查询结果总数.如:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">queryNewUser
===
select * from user order by id desc ;

queryNewUser$count
===
select count(1) from user</code></pre>
</div>
</div>
<div class="paragraph">
<p>大部分情况下,都不需要2个sql来完成,一个sql也可以,要求使用page函数或者pageTag标签,这样才能同时获得查询结果集总数和当前查询的结果</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">queryNewUser
===
select
@pageTag(){
a.*,b.name role_name
@}
from user a left join b ...</code></pre>
</div>
</div>
<div class="paragraph">
<p>如上sql,会在查询的时候转为俩条sql语句</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">select count(1) from user a left join b...
select a.*,b.name role_name  from user a left join b...</code></pre>
</div>
</div>
<div class="paragraph">
<p>如果字段较多,为了输出方便,也可以使用pageTag,字段较少,用page函数也可以. ,具体参考pageTag和page函数说明.翻页代码如下</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">//从第一页开始查询,无参数
PageQuery query = new PageQuery();
sql.pageQuery("user.queryNewUser", User.class,query);
System.out.println(query.getTotalPage());
System.out.println(query.getTotalRow());
System.out.println(query.getPageNumber());
List&lt;User&gt; list = query.getList();</code></pre>
</div>
</div>
<div class="paragraph">
<p>PageQuery 对象也提供了 orderBy属性，用于数据库排序，如 "id desc"</p>
</div>
</div>
<div class="sect2">
<h3 id="true-api-3"><a class="anchor" href="#true-api-3"></a>3.4. 更新API</h3>
<div class="sect3">
<h4 id="true-sql-3"><a class="anchor" href="#true-sql-3"></a>3.4.1. 自动生成sql</h4>
<div class="ulist">
<ul>
<li>
<p>public void insert(Class&lt;?&gt; clazz,Object paras)  插入paras到paras关联的表</p>
</li>
<li>
<p>public void insert(Class&lt;?&gt; clazz,Object paras,KeyHolder holder)，插入paras到paras关联的表，如果需要主键，可以通过holder的getKey来获取</p>
</li>
<li>
<p>public int  insert(Class clazz,Object paras,boolean autoAssignKey) 插入paras，并且指定是否自动将数据库主键赋值到paras里</p>
</li>
<li>
<p>public int updateById(Object obj) 根据主键更新，主键通过annotation表示，如果没有，则认为属性id是主键，所有值参与更新</p>
</li>
<li>
<p>public int updateTemplateById(Object obj) 根据主键更新，组件通过annotation表示，如果没有，则认为属性id是主键,属性为null的不会更新</p>
</li>
<li>
<p>public int updateTemplateById(Class&lt;?&gt; clazz，Map paras) 根据主键更新，组件通过clazz的annotation表示，如果没有，则认为属性id是主键,属性为null的不会更新。</p>
</li>
<li>
<p>public int[] updateByIdBatch(List&lt;?&gt; list) 批量更新</p>
</li>
<li>
<p>public void insertBatch(Class clazz,List&lt;?&gt; list) 批量插入数据</p>
</li>
</ul>
</div>
</div>
<div class="sect3">
<h4 id="true-sqlid"><a class="anchor" href="#true-sqlid"></a>3.4.2. 通过sqlid更新</h4>
<div class="ulist">
<ul>
<li>
<p>public int  insert(String sqlId,Object paras,KeyHolder holder) 根据sqlId 插入，并返回主键，主键id由paras对象所指定</p>
</li>
<li>
<p>public int  insert(String sqlId,Object paras,KeyHolder holder,String keyName) 同上，主键由keyName指定</p>
</li>
<li>
<p>public int  insert(String sqlId,Map paras,KeyHolder holder,String keyName)，同上，参数通过map提供</p>
</li>
<li>
<p>public int update(String sqlId, Object obj) 根据sqlid更新</p>
</li>
<li>
<p>public int update(String sqlId, Map&lt;String, Object&gt; paras) 根据sqlid更新，输出参数是map</p>
</li>
<li>
<p>public int[] updateBatch(String sqlId,List&lt;?&gt; list) 批量更新</p>
</li>
<li>
<p>public int[] updateBatch(String sqlId,Map&lt;String, Object&gt;[] maps) 批量更新，参数是个数组，元素类型是map</p>
</li>
</ul>
</div>
</div>
</div>
<div class="sect2">
<h3 id="true-sql-4"><a class="anchor" href="#true-sql-4"></a>3.5. 直接执行SQL</h3>
<div class="sect3">
<h4 id="true-sql-5"><a class="anchor" href="#true-sql-5"></a>3.5.1. 直接执行sql模板语句</h4>
<div class="ulist">
<ul>
<li>
<p>public &lt;T&gt; List&lt;T&gt; execute(String sql,Class&lt;T&gt; clazz, Object paras)</p>
</li>
<li>
<p>public &lt;T&gt; List&lt;T&gt; execute(String sql,Class&lt;T&gt; clazz, Map paras)</p>
</li>
<li>
<p>public int  executeUpdate(String sql,Object paras)  返回成功执行条数</p>
</li>
<li>
<p>public int  executeUpdate(String sql,Map paras) 返回成功执行条数</p>
</li>
</ul>
</div>
</div>
<div class="sect3">
<h4 id="true-jdbc-sql"><a class="anchor" href="#true-jdbc-sql"></a>3.5.2. 直接执行JDBC sql语句</h4>
<div class="ulist">
<ul>
<li>
<p>查询 public &lt;T&gt; List&lt;T&gt; execute(SQLReady p,Class&lt;T&gt; clazz) SQLReady包含了需要执行的sql语句和参数，clazz是查询结果，如</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">	List&lt;User&gt; list = sqlManager.execute(new SQLReady("select * from user where name=? and age = ?","xiandafu",18),User.class);)</code></pre>
</div>
</div>
<div class="ulist">
<ul>
<li>
<p>更新 public int executeUpdate(SQLReady p)  SQLReady包含了需要执行的sql语句和参数，返回更新结果</p>
</li>
<li>
<p>直接使用Connection public &lt;T&gt; T executeOnConnection(OnConnection&lt;T&gt; call),使用者需要实现onConnection方法的call方法，如调用存储过程</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">	String md5 = sql.executeOnConnection(new OnConnection&lt;String&gt;(){

			@Override
			public String call(Connection conn) throws SQLException {
				CallableStatement cstmt = conn.prepareCall("{ ? = call md5( ? ) }");
				// 其他代码
				return true;
			}

		});</code></pre>
</div>
</div>
</div>
</div>
<div class="sect2">
<h3 id="true--4"><a class="anchor" href="#true--4"></a>3.6. 其他</h3>
<div class="sect3">
<h4 id="true--5"><a class="anchor" href="#true--5"></a>3.6.1. 强制使用主或者从</h4>
<div class="ulist">
<ul>
<li>
<p>public void useMaster(DBRunner f)  DBRunner里的beetlsql调用将使用主数据库库</p>
</li>
<li>
<p>public void useSlave(DBRunner f) DBRunner里的beetlsql调用将使用从数据库库</p>
</li>
</ul>
</div>
</div>
<div class="sect3">
<h4 id="true-pojo-sq"><a class="anchor" href="#true-pojo-sq"></a>3.6.2. 生成Pojo代码和SQ片段</h4>
<div class="ulist">
<ul>
<li>
<p>genPojoCodeToConsole(String table), 根据表名生成pojo类，输出到控制台.</p>
</li>
<li>
<p>genSQLTemplateToConsole(String table),生成查询，条件，更新sql模板，输出到控制台。</p>
</li>
<li>
<p>genPojoCode(String table,String pkg,String srcPath,GenConfig config) 根据表名，包名，生成路径，还有配置，生成pojo代码</p>
</li>
<li>
<p>genPojoCode(String table,String pkg,GenConfig config)  同上，生成路径自动是项目src路径，或者src/main/java (如果是maven工程)</p>
</li>
<li>
<p>genPojoCode(String table,String pkg),同上，采用默认的生成配置</p>
</li>
<li>
<p>genSQLFile(String table), 同上，但输出到工程，成为一个sql模版,sql模版文件的位置在src目录下，或者src／main／resources（如果是maven）工程.</p>
</li>
<li>
<p>genALL(String pkg,GenConfig config,GenFilter filter)   生成所有的pojo代码和sql模版，</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">sql.genAll("com.test", new GenConfig(), new GenFilter(){
	public boolean accept(String tableName){
		if(tableName.equalsIgnoreCase("user")){
			return true;
		}else{
			return false;
		}
		// return false
	}
});</code></pre>
</div>
</div>
<div class="paragraph">
<p>第一个参数是pojo类包名，GenConfig是生成pojo的配置，GenFilter 是过滤，返回true的才会生成。如果GenFilter为null，则数据库所有表都要生成</p>
</div>
<div class="sidebarblock">
<div class="content">
<div class="title">警告</div>
<div class="paragraph">
<p>必须当心覆盖你掉你原来写好的类和方法，不要轻易使用genAll，如果你用了，最好立刻将其注释掉，或者在genFilter写一些逻辑保证不会生成所有的代码好sql模板文件</p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="true--6"><a class="anchor" href="#true--6"></a>4. 命名转化，表和列名映射</h2>
<div class="sectionbody">
<div class="paragraph">
<p>Beetlsql 默认提供了三种列明和属性名的映射类。</p>
</div>
<div class="ulist">
<ul>
<li>
<p>DefaultNameConversion   数据库名和java属性名保持一致，如数据库表User，对应Java类也是User，数据库列是sysytemId,则java属性也是systemId，反之亦然</p>
</li>
<li>
<p>UnderlinedNameConversion 将数据库下划线去掉，首字母大写，如数据库是SYS_USER（oralce数据库的表和属性总是大写的), 则会改成SysUser</p>
</li>
<li>
<p>JPANameConversion 支持JPA方式的映射，适合不能用确定的映射关系</p>
</li>
<li>
<p>自定义命名转化，如果以上3个都不合适,可以自己实现一个命名转化。实现DefaultNameConversion实现方式</p>
</li>
<li>
<p>因为数据库表和列都忽略大小写区别，所以，实现NameConversion也不需要考虑大小写</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">public class DefaultNameConversion extends NameConversion {

	@Override
	public String getTableName(Class&lt;?&gt; c) {
		Table table = (Table)c.getAnnotation(Table.class);
		if(table!=null){
			return table.name();
		}
		return c.getSimpleName();
	}

	@Override
	public String getColName(Class&lt;?&gt; c, String attrName) {
		return attrName;
	}

	@Override
	public String getPropertyName(Class&lt;?&gt; c, String colName) {
		return colName;
	}

}</code></pre>
</div>
</div>
<div class="paragraph">
<p>如果有特殊的表并不符合DefaultNameConversion实现方式，你可以重新实现上面的三个方法</p>
</div>
</div>
</div>
<div class="sect1">
<h2 id="true--7"><a class="anchor" href="#true--7"></a>5. 复合主键</h2>
<div class="sectionbody">
<div class="paragraph">
<p>beetlsql 支持复合主键，无需像其他dao工具那样创建一个特别的主键对象，主键对象就是实体对象本生</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">CREATE TABLE `party` (
  `id1` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id1`,`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;</code></pre>
</div>
</div>
<div class="paragraph">
<p>Party代码如下</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">public class Party  {
	private Integer id1 ;
	private Integer id2 ;
	private String name ;
	//忽略其他 getter setter方法
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>根据主键获取Party</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">Party key = new Party();
key.setId1(1);
key.setId2(2);
Party party = sql.unique(Party.class, key);</code></pre>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="true-mapper"><a class="anchor" href="#true-mapper"></a>6. 使用Mapper</h2>
<div class="sectionbody">
<div class="paragraph">
<p>SQLManager 提供了所有需要知道的API，但通过sqlid来访问sql有时候还是很麻烦，因为需要手敲字符串，另外参数不是map就是para，对代码理解没有好处，BeetlSql支持Mapper，将sql文件映射到一个interface。如下示例</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">public interface UserDao extends BaseMapper&lt;User&gt; {
	// 使用"user.getCount"语句,无参数
	public int getCount();
	//使用"user.setUserStatus" 语句
	public void setUserStatus(Map paras); //更新用户状态
	public void setUserAnnotherStatus(User user); //更新用户状态
	//使用"user.findById", 传入参数id
	public User findById(@Param("id") Integer id);
	//or 使用params，一一对应
	@SqlStatement(params="id,status")
	public User findByIdAndStatus( Integer id,Integer status);
	//翻页查询，使用"user.queryNewUser"
	public void queryNewUser(PageQuery query) ;
	// 使用_st,_sz 翻页
	@SqlStatement(params="name,age,_st,_sz")
	public List&lt;User&gt; queryUser( String name, Integer age,int start, int size);
	//使用sqlready
	@Sql(value=" update user set age = ? where id = ? ")
	public void updateAge(int age,int id);

	@Sql(value=" select name from user",returnType=String.class)
	public List&lt;String&gt; allNames();

}</code></pre>
</div>
</div>
<div class="ulist">
<ul>
<li>
<p>Interface 可以继承BaseMapper，这样可以使用BaseMapper的一些公共方法，如insert，unqiue,updateById,deleteById等，也可以不继承</p>
</li>
<li>
<p>Interface里的方法名与Sql文件对应，如果方法名对应错了，会在调用的时候报错找不到sql。</p>
</li>
<li>
<p>方法参数可以是一个Object,或者是Map，这样，BeetlSql 自动识别为 sql的参数，也可以使用注解@Param来标注，或者混合这俩种情况
如:</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">public void setUserStatus(Map paras,@Param("name") String name);
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>方法如果是查询语句，可以使用@RowStart，@RowSize 作为翻页参数，BeetlSQL将自动完成翻页功能</p>
</div>
<div class="paragraph">
<p>注意 BeetlSQL 会根据 对应的方法对应的SQL语句，解析开头，如果是select开头，就认为是select操作，同理还有update，delete，insert。如果sql 模板不是以这些关键字开头，则需要使用注解 @SqlStatement</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">@SqlStatement(type=SqlStatementType.INSERT)
public KeyHolder newUser(User user);// 添加用户</code></pre>
</div>
</div>
<div class="paragraph">
<p>SqlStatement 也可在params申明参数名称</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">public List&lt;User&gt; queryUser(@Param("name") String name,@Param("age") Integer age,@RowStart int start,@RowSize int size);
// or
@SqlStatement(params="name,age,_st,_se")
public List&lt;User&gt; queryUser(String name,Integer age,int start,int size);</code></pre>
</div>
</div>
<div class="paragraph">
<p>params列表按照参数申明顺序,_root保留字 代表查询得root对象,_st 代表翻页其实,_sz 代表返回行数</p>
</div>
<div class="paragraph">
<p>方法返回值和参数会暗示对应的SQLManager操作，如下是规则</p>
</div>
<div class="ulist">
<ul>
<li>
<p>查询语句返回的是List，则对应SQLManager.select</p>
</li>
<li>
<p>查询语句返回的是Pojo，原始类型等非List类型，则对应的SQLManager.selectSignle，如上面的getCount</p>
</li>
<li>
<p>insert 语句 如果有KeyHolder，则表示需要获取主键，对应SQLManager.insert(&#8230;&#8203;.,keyHolder)方法</p>
</li>
<li>
<p>参数列表里只允许有一个Pojo或者Map，作为查询参数_root，否则，需要加上@Param</p>
</li>
<li>
<p>参数列表里如果有List 或者Map[],则期望对应的是一个updateBatch操作</p>
</li>
<li>
<p>参数列表里如果@RowStart ,@RowSize,则认为是翻页语句</p>
</li>
<li>
<p>参数里如果有PageQuery,则认为是翻页查询</p>
</li>
<li>
<p>在查询中，返回的是List，但类型非Mapper指定的类型，无论是用@Sql ,还是 @SqlStatement，需要用returnType来说明，如上例子allNames 返回一个List&lt;String&gt;</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>Mapper 也支持使用JDBC SQL，这时候需要采用Sql注解</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">@Sql(value=" update user set age = ? where id = ? ")
public void updateAge(int age,int id);
@Sql("select * from user  ")
public List&lt;User&gt; selectAll();</code></pre>
</div>
</div>
<div class="paragraph">
<p>使用Mapper能增加Dao维护性，并能提高开发效率，建议在项目中使用。</p>
</div>
</div>
</div>
<div class="sect1">
<h2 id="truebeetlsql-annotation"><a class="anchor" href="#truebeetlsql-annotation"></a>7. BeetlSQL Annotation</h2>
<div class="sectionbody">
<div class="paragraph">
<p>对于自动生成的sql，默认不需要任何annotaton，类名对应于表名（通过NameConverstion类），getter方法的属性名对应于列明（也是通过NameConverstion类），但有些情况还是需要anntation。</p>
</div>
<div class="sect2">
<h3 id="true-autoid-assignid-seqid"><a class="anchor" href="#true-autoid-assignid-seqid"></a>7.1. @AutoID 和 @AssignID ，@SeqID</h3>
<div class="ulist">
<ul>
<li>
<p>@AutoID,作用于getter方法，告诉beetlsql，这是自增主键,对应于数据自增长</p>
</li>
<li>
<p>@AssignID，作用于getter方法，告诉beetlsql，这是程序设定</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">@AssignID()
public Long getId() {
	return id;
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>代码设定主键允许像@AssignID 传入id的生成策略以自动生成序列，beetl默认提供了一个snowflake算法，一个用于分布式环境的id生成器(<a href="https://github.com/twitter/snowflake" class="bare">https://github.com/twitter/snowflake</a>)</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">@AssignID("simple")
public Long getId() {
	return id;
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>simple 是beetlsql提供的一个默认的snowflake实现，你可以通过sqlManager自己注册id生成器</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">sqlManager.addIdAutonGen("uuid2", new IDAutoGen(){

		@Override
		public Object nextID(String params) {
			return "hi"+new Random().nextInt(10000);
		}

});</code></pre>
</div>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">@AssignID("uuid2")
public Long getId() {
	return id;
}</code></pre>
</div>
</div>
<div class="ulist">
<ul>
<li>
<p>@SeqID(name="xx_seq"，作用于getter方法，告诉beetlsql，这是序列主键。</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>对于属性名为id的自增主键，不需要加annotation，beetlsql默认就是@AutoID</p>
</div>
<div class="sidebarblock">
<div class="content">
<div class="title">备注</div>
<div class="ulist">
<ul>
<li>
<p>对于支持多种数据库的，这些annotation可以叠加在一起</p>
</li>
</ul>
</div>
</div>
</div>
</div>
<div class="sect2">
<h3 id="true-tail"><a class="anchor" href="#true-tail"></a>7.2. @Tail</h3>
<div class="paragraph">
<p>@Tail作用于类上，表示该对象是混合模型，参考下一章混合模型,sql查询无法在pojo映射的列或者结果集将使用Tail指定的方法</p>
</div>
</div>
<div class="sect2">
<h3 id="true-columnignore"><a class="anchor" href="#true-columnignore"></a>7.3. @ColumnIgnore</h3>
<div class="paragraph">
<p>在beetlsql 内置的insert或者update方法的时候，使用此注解的字段（作用于getter方法）将根据注解的属性来决定是否忽略此字段</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">    @ColumnIgnore(insert=true,update=false)
	public Date getBir(){
		return  bir;
	}</code></pre>
</div>
</div>
<div class="paragraph">
<p>如上例子，插入的时候忽略bir属性（往往是因为数据库指定了默认值为当前时间），更新的时候不能忽略
@ColumnIgnore的insert默认是true，update是false，因此也可以直接用 @ColumnIgnore()</p>
</div>
</div>
<div class="sect2">
<h3 id="true-enummapping"><a class="anchor" href="#true-enummapping"></a>7.4. @EnumMapping</h3>
<div class="paragraph">
<p>对于Entity使用了枚举作为属性值，可以再枚举类上定义EnumMapping，指出如何将枚举与数据库值互相转化，有四种方法</p>
</div>
<div class="ulist">
<ul>
<li>
<p>如果没有使用@EnumMapping，则使用枚举的名称作为属性</p>
</li>
<li>
<p>@EnumMapping(EnumMapping.EnumType.STRING) 同上，使用枚举名称作为属性，数据库对应列应该是字符列</p>
</li>
<li>
<p>@EnumMapping(EnumMapping.EnumType.ORDINAL) 使用枚举的顺序作为属性，数据库对应列应该是int类型，用此作为映射需要防止重构枚举的时候导致数据库也重构，应该慎用</p>
</li>
<li>
<p>@EnumMapping(“xxx”)，如果不是上面的的定义，则beetlsql会查找枚举类的xxx属性，用这个值作为属性，比如</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">@EnumMapping("value")
	public enum Color {
		RED("RED",1),BLUE ("BLUE",2);
		private String name;
		private int value;
		private Color(String name, int value) {
		    this.name = name;
		    this.value = value;
		}
		public String getName() {
			return name;
		}
		public void setName(String name) {
			this.name = name;
		}
		public int getValue() {
			return value;
		}
		public void setValue(int value) {
			this.value = value;
		}

	}</code></pre>
</div>
</div>
<div class="paragraph">
<p>beetlsq 会获取枚举的value属性（调用getValue)来获取枚举属性值</p>
</div>
</div>
<div class="sect2">
<h3 id="true-table"><a class="anchor" href="#true-table"></a>7.5. @Table</h3>
<div class="paragraph">
<p>标签 @Table(name="xxxx")  告诉beetlsql，此类对应xxxx表。比如数据库有User表，User类对应于User表，也可以创建一个UserQuery对象，也对应于User表</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">@Table(name="user")
public class QueryUser ..</code></pre>
</div>
</div>
<div class="paragraph">
<p>注：可以为对象指定一个数据库shcema，如name="cms.user",此时将访问cms库（或者cms用户，对不同的数据库，称谓不一样）下的user数据表*</p>
</div>
</div>
<div class="sect2">
<h3 id="true-tabletemplate"><a class="anchor" href="#true-tabletemplate"></a>7.6. @TableTemplate</h3>
<div class="ulist">
<ul>
<li>
<p>@TableTemplate() 用于模板查询，如果没有任何值，将按照主键降序排，也就是order by 主键名称 desc</p>
</li>
<li>
<p>@DateTemplate()，作用于日期字段的getter方法上，有俩个属性accept 和  compare 方法，分别表示 模板查询中，日期字段如果不为空，所在的日期范围，如</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">@DateTemplate(accept="minDate,maxDate",compare="&gt;=,&lt;")
	public Date getDate() {
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>在模板查询的时候，将会翻译成</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">@if(!isEmpty(minDate)){
 and date&gt;=#minDate#
@}
@if(!isEmpty(maxDate)){
 and date&lt;#maxDate#
@}</code></pre>
</div>
</div>
<div class="sidebarblock">
<div class="content">
<div class="title">注意</div>
<div class="paragraph">
<p>minDate,maxDate 是俩个额外的变量,需要定义到pojo类里，DateTemplate也可以有默认值，如果@DateTemplate()，相当于@DateTemplate(accept="min日期字段,max日期字段",compare="&gt;=,&lt;")</p>
</div>
</div>
</div>
</div>
<div class="sect2">
<h3 id="truemapper"><a class="anchor" href="#truemapper"></a>7.7. Mapper相关注解</h3>
<div class="ulist">
<ul>
<li>
<p>Mapper中的注解，包括常用的	SqlStatement ，SqlStatementType ，Sql,Param 还有不常用的 RowSize ，RowStart，具体参考Mapper</p>
</li>
</ul>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="truebeetlsql-3"><a class="anchor" href="#truebeetlsql-3"></a>8. BeetlSQL 数据模型</h2>
<div class="sectionbody">
<div class="paragraph">
<p>BeetlSQL是一个全功能DAO工具，支持的模型也很全面，包括</p>
</div>
<div class="ulist">
<ul>
<li>
<p>Pojo, 也就是面向对象Java Objec。Beetlsql操作将选取Pojoe属性和sql列的交集。额外属性和额外列将忽略.</p>
</li>
<li>
<p>Map/List, 对于一些敏捷开发，可以直接使用Map/List 作为输入输出参数</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">List&lt;Map&lt;String,Object&gt;&gt; list = sqlManager.select("user.find",Map.class,paras)</code></pre>
</div>
</div>
<div class="ulist">
<ul>
<li>
<p>混合模型，推荐使用混合模型。兼具灵活性和更好的维护性。Pojo可以实现Tail（尾巴的意思)，或者继承TailBean，这样查询出的ResultSet 除了按照pojo进行映射外，无法映射的值将按照列表/值保存。如下一个混合模型:</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">/*混合模型*/
public User extends TailBean{
	private int id ;
	pirvate String name;
	private int roleId;
	/*以下是getter和setter 方法*/
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>对于sql语句:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">selectUser
===
select u.*,r.name r_name from user u left join role r on u.roleId=r.id .....</code></pre>
</div>
</div>
<div class="paragraph">
<p>执行查询的时候</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">List&lt;User&gt; list = sqlManager.select("user.selectUser",User.class,paras);
for(User user:list){
	System.out.println(user.getId());
	System.out.println(user.get("rName"));

}</code></pre>
</div>
</div>
<div class="paragraph">
<p>程序可以通过get方法获取到未被映射到pojo的值，也可以在模板里直接 ${user.rName}  显示（对于大多数模板引擎都支持）</p>
</div>
<div class="paragraph">
<p>另外一种更自由的实现混合模型的方法是在目标Pojo上采用注解@Tail，如果注解不带参数，则默认会调用set(String,Object) 方法来放置额外的查询属性，否则，依据注解的set参数来确定调用方法</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">@Tail(set="addValue")
public class User  {
	private Integer id ;
	private Integer age ;
	public User addValue(String str,Object ok){
		ext.put(str, ok);
		return this;
	}</code></pre>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="truemarkdown"><a class="anchor" href="#truemarkdown"></a>9. Markdown方式管理</h2>
<div class="sectionbody">
<div class="paragraph">
<p>BeetlSQL集中管理SQL语句，SQL 可以按照业务逻辑放到一个文件里，文件名的扩展名是md或者sql。如User对象放到user.md 或者 user.sql里，文件可以按照模块逻辑放到一个目录下。文件格式抛弃了XML格式，采用了Markdown，原因是</p>
</div>
<div class="ulist">
<ul>
<li>
<p>XML格式过于复杂，书写不方便</p>
</li>
<li>
<p>XML 格式有保留符号，写SQL的时候也不方便，如常用的&lt; 符号 必须转义</p>
</li>
<li>
<p>MD 格式本身就是一个文档格式，也容易通过浏览器阅读和维护</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>目前SQL文件格式非常简单，仅仅是sqlId 和sql语句本身，如下</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-text" data-lang="text">文件一些说明，放在头部可有可无，如果有说明，可以是任意文字
SQL标示
===
以*开头的注释
SQL语句

SQL标示2
===
SQL语句 2</code></pre>
</div>
</div>
<div class="paragraph">
<p>所有SQL文件建议放到一个sql目录，sql目录有多个子目录，表示数据库类型，这是公共SQL语句放到sql目录下，特定数据库的sql语句放到各自自目录下
当程序获取SQL语句得时候，先会根据数据库找特定数据库下的sql语句，如果未找到，会寻找sql下的。如下代码</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">List&lt;User&gt; list = sqlManager.select("user.select",User.class);</code></pre>
</div>
</div>
<div class="paragraph">
<p>SqlManager 会根据当前使用的数据库，先找sql/mysql/user.md 文件，确认是否有select语句，如果没有，则会寻找sql/user.md</p>
</div>
<div class="sidebarblock">
<div class="content">
<div class="title">注</div>
<div class="ulist">
<ul>
<li>
<p>注释是以* 开头，注释语句不作为sql语句</p>
</li>
<li>
<p>默认的ClasspathLoader采用了这种方法，你可以实现SQLLoader来实现自己的格式和sql存储方式，如数据库存储</p>
</li>
</ul>
</div>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="truesql-2"><a class="anchor" href="#truesql-2"></a>10. SQL 注释</h2>
<div class="sectionbody">
<div class="paragraph">
<p>对于采用Markdown方式，可以采用多种方式对sql注释。</p>
</div>
<div class="ulist">
<ul>
<li>
<p>采用sql 自己的注释符号，"-- " ,优点是适合java和数据库sql之间互相迁移，如</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">select * from user where
--  status 代表状态
statu = 1</code></pre>
</div>
</div>
<div class="ulist">
<ul>
<li>
<p>采用beetl注释</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">select * from user where
@ /* 这些sql语句被注释掉
statu = 1
@ */</code></pre>
</div>
</div>
<div class="ulist">
<ul>
<li>
<p>在sqlId 的=== 紧挨着的下一行 后面连续使用“*”作为sql整个语句注释</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">selectByUser
==
* 这个sql语句用来查询用户的
* status =1 表示查找有效用户

select * from user where status = 1</code></pre>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="true-beetlsql"><a class="anchor" href="#true-beetlsql"></a>11. (重要) 配置beetlsql</h2>
<div class="sectionbody">
<div class="paragraph">
<p>beetlsql 配置文件是 btsql-ext.properties，位于classpath 根目录下，如果没有此文件，beetlsql将使用系统默认配置，如
* 是开发模式，beetlsql每次运行sql都会检测sql文件是否变化，并重新加载
* 字符集，是系统默认的字符集
* 翻页默认总是从1开始，对于oralce数据库来说，翻页起始参数正合适。对于mysql其他数据库来说，beetlsql，翻页参数变成n-1.一般你不需要关心</p>
</div>
<div class="sect2">
<h3 id="true--8"><a class="anchor" href="#true--8"></a>11.1. 开发模式和产品模式</h3>
<div class="paragraph">
<p>beetlsql默认是开发模式，因此修改md的sql文件，不需要重启。但建议线上不要使用开发模式，因为此模式会每次sql调用都会检测md文件是否变化。可以通过修改/btsql-ext.properties ,修改如下属性改为产品模式</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-text" data-lang="text">PRODUCT_MODE = true</code></pre>
</div>
</div>
</div>
<div class="sect2">
<h3 id="true--9"><a class="anchor" href="#true--9"></a>11.2. 模板字符集</h3>
<div class="paragraph">
<p>默认sql模板文件采用的是系统默认字符集，可以更改配置采用指定的字符集</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-text" data-lang="text">CHARSET = UTF-8</code></pre>
</div>
</div>
</div>
<div class="sect2">
<h3 id="true-0-1"><a class="anchor" href="#true-0-1"></a>11.3. 翻页起始参数是0还是1</h3>
<div class="paragraph">
<p>默认认为1对应于翻页的第一条记录，如果你习惯mysql 那种0对应于第一条记录，则需要配置OFFSET_START_ZERO，设置为true</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-text" data-lang="text">OFFSET_START_ZERO =  true</code></pre>
</div>
</div>
<div class="paragraph">
<p>无论是从0开始还是从开始，都不影响beetlsql根据特定数据库翻译成目标数据库的sql语句，这只是一个个人习惯，如系统只有mysql数据库
那从0开始，比较符合mysql的习惯。</p>
</div>
</div>
<div class="sect2">
<h3 id="true--10"><a class="anchor" href="#true--10"></a>11.4. 自定义方法和标签函数</h3>
<div class="paragraph">
<p>可以在sql模板中使用自定义方法和标签函数，具体请参考beetl使用说明，如下是默认配置</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-text" data-lang="text">FN.use = org.beetl.sql.core.engine.UseFunction
FN.globalUse = org.beetl.sql.core.engine.GlobalUseFunction
FN.text = org.beetl.sql.core.engine.TextFunction
FN.join = org.beetl.sql.ext.JoinFunction
FN.isEmpty=org.beetl.sql.ext.EmptyExpressionFunction
FN.page=org.beetl.sql.core.engine.PageQueryFuntion
TAG.trim= org.beetl.sql.core.engine.TrimTag
TAG.pageTag= org.beetl.sql.core.engine.PageQueryTag</code></pre>
</div>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="truesql-beetl"><a class="anchor" href="#truesql-beetl"></a>12. SQL 模板基于Beetl实现</h2>
<div class="sectionbody">
<div class="paragraph">
<p>SQL语句可以动态生成，基于Beetl语言，这是因为</p>
</div>
<div class="ulist">
<ul>
<li>
<p>beetl执行效率高效 ，因此对于基于模板的动态sql语句，采用beetl非常合适</p>
</li>
<li>
<p>beetl 语法简单易用，可以通过半猜半式的方式实现，杜绝myBatis这样难懂难记得语法。BeetlSql学习曲线几乎没有</p>
</li>
<li>
<p>利用beetl可以定制定界符号，完全可以将sql模板定界符好定义为数据库sql注释符号，这样容易在数据库中测试，如下也是sql模板（定义定界符为"--:" 和 null,null是回车意思);</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-text" data-lang="text">selectByCond
===
select * from user where 1=1
--:if(age!=null)
age=#age#
--:}</code></pre>
</div>
</div>
<div class="ulist">
<ul>
<li>
<p>beetl 错误提示非常友好，减少写SQL脚本编写维护时间</p>
</li>
<li>
<p>beetl 能容易与本地类交互（直接访问Java类），能执行一些具体的业务逻辑 ，也可以直接在sql模板中写入模型常量，即使sql重构，也会提前解析报错</p>
</li>
<li>
<p>beetl语句易于扩展，提供各种函数，比如分表逻辑函数，跨数据库的公共函数等</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>如果不了解beetl，可先自己尝试按照js语法来写sql模板，如果还有疑问，可以查阅官网 <a href="http://ibeetl.com" class="bare">http://ibeetl.com</a></p>
</div>
</div>
</div>
<div class="sect1">
<h2 id="truebeetl"><a class="anchor" href="#truebeetl"></a>13. Beetl 入门</h2>
<div class="sectionbody">
<div class="paragraph">
<p>Beetl 语法类似js，java，如下做简要说明，使用可以参考   <a href="http://ibeetl.com" class="bare">http://ibeetl.com</a> , 或者在线体验  <a href="http://ibeetl.com:8080/beetlonline/" class="bare">http://ibeetl.com:8080/beetlonline/</a></p>
</div>
<div class="sect2">
<h3 id="true--11"><a class="anchor" href="#true--11"></a>13.1. 定界符号</h3>
<div class="paragraph">
<p>默认的定界符号是@ 和 回车。 里面可以放控制语句，表达式等语，，站位符号是##,站位符号默认是输出？，并在执行sql的传入对应的值。如果想在占位符号输出变量值，则需要使用text函数</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-javascript" data-lang="javascript">@if(!isEmpty(name)){
	and name = #name#
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>如果想修改定界符，可以增加一个/btsql-ext.properties. 设置如下属性</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-text" data-lang="text">DELIMITER_PLACEHOLDER_START=#
DELIMITER_PLACEHOLDER_END=#
DELIMITER_STATEMENT_START=@
DELIMITER_STATEMENT_END=</code></pre>
</div>
</div>
<div class="paragraph">
<p>beetlsql 的其他属性也可以在此文件里设置</p>
</div>
</div>
<div class="sect2">
<h3 id="true--12"><a class="anchor" href="#true--12"></a>13.2. 变量</h3>
<div class="paragraph">
<p>通过程序传入的变量叫全局变量，可以在sql模板里使用，也可以定义变量，如</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-javascript" data-lang="javascript">@var count = 3;
@var status = {"a":1}  //json变量</code></pre>
</div>
</div>
</div>
<div class="sect2">
<h3 id="true--13"><a class="anchor" href="#true--13"></a>13.3. 算数表达式</h3>
<div class="paragraph">
<p>同js，如a+1-b%30, i++ 等</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">select * from user where name like #'%'+name+'%'#</code></pre>
</div>
</div>
</div>
<div class="sect2">
<h3 id="true--14"><a class="anchor" href="#true--14"></a>13.4. 逻辑表达式</h3>
<div class="paragraph">
<p>有“&amp;&amp;” “||” ，还有 “！”，分别表示与，或，非， beetl也支持三元表达式</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-javascript" data-lang="javascript">#user.gender==1?'女':'男'#</code></pre>
</div>
</div>
</div>
<div class="sect2">
<h3 id="true--15"><a class="anchor" href="#true--15"></a>13.5. 控制语句</h3>
<div class="ulist">
<ul>
<li>
<p>if else 这个同java，c，js。</p>
</li>
<li>
<p>for,循环语句，如for(id:ids){}</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-javascript" data-lang="javascript">select * from user where status in (
@for(id in ids){
#id#  #text(idLP.last?"":"," )#
@}</code></pre>
</div>
</div>
<div class="sidebarblock">
<div class="content">
<div class="title">注意</div>
<div class="ulist">
<ul>
<li>
<p>变量名＋LP 是一个内置变量，包含了循环状态，具体请参考beetl文档，text方法表示直接输出文本而不是符号“？”</p>
</li>
<li>
<p>关于 sql中的in，可以使用内置的join方法更加方便</p>
</li>
</ul>
</div>
</div>
</div>
<div class="ulist">
<ul>
<li>
<p>while 循环语句 ，如while(i&lt;count))</p>
</li>
</ul>
</div>
</div>
<div class="sect2">
<h3 id="true--16"><a class="anchor" href="#true--16"></a>13.6. 访问变量属性</h3>
<div class="ulist">
<ul>
<li>
<p>如果是对象，直接访问属性名，user.name</p>
</li>
<li>
<p>如果是Map，用key访问 map["key"];</p>
</li>
<li>
<p>如果是数组或者list，用索引访问，如list[1],list[i];</p>
</li>
<li>
<p>可以直采用java方式访问变量的方法和属性，如静态类Constatns</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">public class Constatns{
	public static int 	RUNNING = 0;
	public static User getUser(){}
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>直接以java方式访问，需要再变量符号前加上@，可以在模板里访问</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">select * from user where status = #@Constatns.RUNNING# and id = #@Constatns.getUser().getId()#</code></pre>
</div>
</div>
<div class="paragraph">
<p>注意，如果Constants 类 没有导入进beetl，则需要带包名，导入beetl方法是配置IMPORT_PACKAGE=包名.;包名.</p>
</div>
</div>
<div class="sect2">
<h3 id="true--17"><a class="anchor" href="#true--17"></a>13.7. 判断对象非空空</h3>
<div class="paragraph">
<p>可以采用isEmpty判断变量表达式是否为空(为null)，是否存在，如果是字符串，是否是空字符串，如</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">if(isEmpty(user)||isEmpty(role.name))</code></pre>
</div>
</div>
<div class="paragraph">
<p>也可以用传统方法判断，如</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">if(user==null) or if(role.name!=null))</code></pre>
</div>
</div>
<div class="paragraph">
<p>变量有可能不存在，可用hasH函数或者需要使用安全输出符号，如</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">if(null==user.name!))
//or
if(has(user))</code></pre>
</div>
</div>
<div class="paragraph">
<p>变量表达式后面跟上"!" 表示如果变量不存在，则为！后面的值，如果！后面没有值，则为null</p>
</div>
</div>
<div class="sect2">
<h3 id="true--18"><a class="anchor" href="#true--18"></a>13.8. 调用方法</h3>
<div class="paragraph">
<p>同js，唯一值得注意的是，在占位符里调用text方法，会直接输出变量而不是“？”，其他以db开头的方式也是这样。架构师可以设置SQLPlaceholderST.textFunList.add(xxxx) 来决定那些方法在占位符号里可以直接输出文本而不是符号"?"</p>
</div>
<div class="paragraph">
<p>beetl提供了很多内置方法，如print，debug,isEmpty,date等，具体请参考文档</p>
</div>
</div>
<div class="sect2">
<h3 id="true--19"><a class="anchor" href="#true--19"></a>13.9. 自定义方法</h3>
<div class="paragraph">
<p>通过配置btsql-ext.properties, 可以注册自己定义的方法在beetlsql里使用，如注册一个返回当前年份的函数，可以在btsql-ext.properties加如下代码</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-text" data-lang="text">FN.db.year= com.xxx.YearFunction</code></pre>
</div>
</div>
<div class="paragraph">
<p>这样在模板里,可以调用db.year() 获得当前年份。YearFunction 需要实现Function的 call方法，如下是个简单代码</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">public class YearFunction implements Function{
	public String call(Object[] paras, Context ctx){
		return "2015";
	}
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>关于如何完成自定义方法，请参考 ibeetl 官方文档</p>
</div>
</div>
<div class="sect2">
<h3 id="true--20"><a class="anchor" href="#true--20"></a>13.10. 内置方法</h3>
<div class="ulist">
<ul>
<li>
<p>print println 输出，同js，如print("table1");</p>
</li>
<li>
<p>has， 判断是否有此全局变量;</p>
</li>
<li>
<p>isEmpty 判断表达式是否为空，不存在，空字符串，空集合都返回true;</p>
</li>
<li>
<p>debug  将变量输出到控制台，如 debug(user);</p>
</li>
<li>
<p>text 输出，但可用于占位符号里</p>
</li>
<li>
<p>page 函数，用于在PageQuery翻页里，根据上下问决定输出count(1) 或者count(*),如果有参数，则按照参数输出</p>
</li>
<li>
<p>join, 用逗号连接集合或者数组，并输出？，用于in，如</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">select * from user where status in ( #join(ids)＃)
			-- 输出成  select * from user where status in (?,?,?)</code></pre>
</div>
</div>
<div class="ulist">
<ul>
<li>
<p>use  参数是同一个md文件的sqlid，类似mybatis的 sql功能，如</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre>condtion
===
where 1=1 and name = #name#

selectUser
===
select * from user #use("condition")#</pre>
</div>
</div>
<div class="paragraph">
<p>globalUse 参数是其他文件的globalUse，如<mark>globalUse("share.accessControl")</mark>,将访问share.md(sql)文件的accessControl片段</p>
</div>
<div class="ulist">
<ul>
<li>
<p>page 用于pagequery,但beetlsql 使用pagequery查询,会将sql模板翻译成带有count(1),和列表名的俩个sql语句,因此必须使用page函数或者pageTag标签</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">queryNewUser
===
select #page()# from user</code></pre>
</div>
</div>
<div class="paragraph">
<p>如果无参数,则在查询的时候解释成 *,如果有参数,则解释成列名,如 page("a.name,a.id,b.name role_name") ,如果列名较多,可以使用pageTag</p>
</div>
</div>
<div class="sect2">
<h3 id="true--21"><a class="anchor" href="#true--21"></a>13.11. 标签功能</h3>
<div class="ulist">
<ul>
<li>
<p>beetlsql 提供了trim标签函数，用于删除标签体最后一个逗号，这可以帮助拼接条件sql，如</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">updateStatus
===

update user set
@trim(){
@if(!isEmpty(age){
age = #age# ,
@} if(!isEmpty(status){
status = #status#,
@}
@}
where id = #id#</code></pre>
</div>
</div>
<div class="paragraph">
<p>trim 标签可以删除 标签体里的最后一个逗号.trim 也可以实现类似mybatis的功能，通过传入trim参数prefix，prefixOverrides来完成。具体参考标签api 文档</p>
</div>
<div class="ulist">
<ul>
<li>
<p>pageTag,同page函数,用于pageQuery,如</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">queryNewUser
===
select
@pageTag(){
id,name,status
@}
from user</code></pre>
</div>
</div>
<div class="paragraph">
<p>注:可以参考beetl官网 了解如何开发自定义标签以及注册标签函数</p>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="truedebug"><a class="anchor" href="#truedebug"></a>14. Debug功能</h2>
<div class="sectionbody">
<div class="paragraph">
<p>Debug 期望能在控制台或者日志系统输出执行的sql语句，参数，执行结果以及执行时间，可以采用系统内置的DebugInterceptor 来完成，在构造SQLManager的时候，传入即可</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">SqlManager sqlManager = new SqlManager(source,mysql,loader,nc ,new Interceptor[]{new DebugInterceptor() });</code></pre>
</div>
</div>
<div class="paragraph">
<p>或者通过spring，jfianl这样框架配置完成。使用后，执行beetlsql，会有类似输出</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">┏━━━━━ Debug [user.selectUserAndDepartment] ━━━
┣ SQL：	 select * from user where 1 = 1
┣ 参数：	 []
┣ 位置：	 org.beetl.sql.test.QuickTest.main(QuickTest.java:47)
┣ 时间：	 23ms
┣ 结果：	 [3]
┗━━━━━ Debug [user.selectUserAndDepartment] ━━━</code></pre>
</div>
</div>
<div class="paragraph">
<p>beetlsql会分别输出 执行前的sql和参数，以及执行后的结果和耗费的时间。你可以参考DebugInterceptor 实现自己的调试输出</p>
</div>
</div>
</div>
<div class="sect1">
<h2 id="trueinterceptor"><a class="anchor" href="#trueinterceptor"></a>15. Interceptor功能</h2>
<div class="sectionbody">
<div class="paragraph">
<p>BeetlSql可以在执行sql前后执行一系列的Intercetor，从而有机会执行各种扩展和监控，这比已知的通过数据库连接池做Interceptor更加容易。如下Interceptor都是有可能的</p>
</div>
<div class="ulist">
<ul>
<li>
<p>监控sql执行较长时间语句，打印并收集。TimeStatInterceptor 类完成</p>
</li>
<li>
<p>对每一条sql语句执行后输出其sql和参数，也可以根据条件只输出特定sql集合的sql。便于用户调试。DebugInterceptor完成</p>
</li>
<li>
<p>对sql预计解析，汇总sql执行情况（未完成，需要集成第三方sql分析工具）</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>你也可以自行扩展Interceptor类，来完成特定需求。
如下，在执行数据库操作前会执行befor，通过ctx可以获取执行的上下文参数，数据库成功执行后，会执行after方法</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">public interface Interceptor {
		public void before(InterceptorContext ctx);
		public void after(InterceptorContext ctx);
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>InterceptorContext 如下，包含了sqlId，实际得sql，和实际得参数, 也包括执行结果result。对于查询，执行结果是查询返回的结果集条数，对于更新，返回的是成功条数，如果是批量更新，则是一个数组。可以参考源码DebugInterceptor</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">public class InterceptorContext {
	private String sqlId;
	private String sql;
	private  List&lt;Object&gt; paras;
	private boolean isUpdate = false ;
	private Object result ;
	private Map&lt;String,Object&gt; env  = null;

}</code></pre>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="true--22"><a class="anchor" href="#true--22"></a>16. 内置支持主从数据库</h2>
<div class="sectionbody">
<div class="paragraph">
<p>BeetlSql管理数据源，如果只提供一个数据源，则认为读写均操作此数据源，如果提供多个，则默认第一个为写库，其他为读库。用户在开发代码的时候，无需关心操作的是哪个数据库，因为调用sqlScrip 的 select相关api的时候，总是去读取从库，add/update/delete 的时候，总是读取主库。</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">sqlManager.insert(User.class,user) // 操作主库，如果只配置了一个数据源，则无所谓主从
sqlManager.unique(id,User.class) //读取从库</code></pre>
</div>
</div>
<div class="paragraph">
<p>主从库的逻辑是由ConnectionSource来决定的，如下DefaultConnectionSource 的逻辑</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">@Override
public Connection getConn(String sqlId,boolean isUpdate,String sql,List&lt;?&gt; paras){
	if(this.slaves==null||this.slaves.length==0) return this.getWriteConn(sqlId,sql,paras);
	if(isUpdate) return this.getWriteConn(sqlId,sql,paras);
	int status  = forceStatus.get();
	if(status ==0||status==1){
		return this.getReadConn(sqlId, sql, paras);
	}else{
		return this.getWriteConn(sqlId,sql,paras);
	}
}</code></pre>
</div>
</div>
<div class="ulist">
<ul>
<li>
<p>forceStatus 可以强制SQLManager 使用主或者从数据库。参考api SQLManager.useMaster(DBRunner f)  ，SQLManager.useSlave(DBRunner f)</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>对于于不同的ConnectionSource 完成逻辑不一样，对于spring，jfinal这样的框架，如果sqlManager在事务环境里，总是操作主数据库，如果是只读事务环境
则操作从数据库。如果没有事务环境，则根据sql是查询还是更新来决定。</p>
</div>
<div class="paragraph">
<p>如下是SpringConnectionSource 提供的主从逻辑</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">public Connection getConn(String sqlId,boolean isUpdate,String sql,List paras){
	//只有一个数据源
	if(this.slaves==null||this.slaves.length==0) return this.getWriteConn(sqlId,sql,paras);
	//如果是更新语句，也得走master
	if(isUpdate) return this.getWriteConn(sqlId,sql,paras);
	//如果api强制使用
	int status  = forceStatus.get();
	if(status==1){
		return this.getReadConn(sqlId, sql, paras);
	}else if(status ==2){
		return this.getWriteConn(sqlId,sql,paras);
	}
	//在事物里都用master，除了readonly事物
	boolean inTrans = TransactionSynchronizationManager.isActualTransactionActive();
	if(inTrans){
		boolean  isReadOnly = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
		if(!isReadOnly){
			return this.getWriteConn(sqlId,sql,paras);
		}
	}
	 return this.getReadConn(sqlId, sql, paras);
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>注意，对于使用者来说，无需关心本节说的内容，仅仅供要定制主从逻辑的架构师。</p>
</div>
</div>
</div>
<div class="sect1">
<h2 id="true--23"><a class="anchor" href="#true--23"></a>17. 可以支持更复杂的分库分表逻辑</h2>
<div class="sectionbody">
<div class="paragraph">
<p>开发者也可以通过在Sql 模板里完成分表逻辑而对使用者透明，如下sql语句</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">insert into
	#text("log_"+ getMonth(date())#
	values () ...</code></pre>
</div>
</div>
<div class="paragraph">
<p>注：text函数直接输出表达式到sql语句，而不是输出？。</p>
</div>
<div class="paragraph">
<p>log表示按照一定规则分表，table可以根据输入的时间去确定是哪个表</p>
</div>
<div class="literalblock">
<div class="content">
<pre>select * from
#text("log"+log.date)#
where</pre>
</div>
</div>
<div class="sidebarblock">
<div class="content">
<div class="title">注</div>
<div class="paragraph">
<p>text函数直接输出表达式到sql语句，而不是输出？。</p>
</div>
</div>
</div>
<div class="paragraph">
<p>同样，根据输入条件决定去哪个表，或者查询所有表</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">@ var tables = getLogTables();
@ for(table in tables){
select * from #text(table)#
@		if(!tableLP.isLast) print("union");
@}
where name = #name#</code></pre>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="true--24"><a class="anchor" href="#true--24"></a>18. 跨数据库平台</h2>
<div class="sectionbody">
<div class="paragraph">
<p>如前所述，BeetlSql 可以通过sql文件的管理和搜索来支持跨数据库开发，如前所述，先搜索特定数据库，然后再查找common。另外BeetlSql也提供了一些夸数据库解决方案</p>
</div>
<div class="ulist">
<ul>
<li>
<p>DbStyle 描述了数据库特性，注入insert语句，翻页语句都通过其子类完成，用户无需操心</p>
</li>
<li>
<p>提供一些默认的函数扩展，代替各个数据库的函数，如时间和时间操作函数date等</p>
</li>
<li>
<p>MySqlStyle mysql 数据库支持</p>
</li>
<li>
<p>OracleStyle oralce支持</p>
</li>
<li>
<p>PostgresStyle postgres数据库支持</p>
</li>
<li>
<p>其他还有SQLServer,H2,SQLLite 数据库支持</p>
</li>
</ul>
</div>
</div>
</div>
<div class="sect1">
<h2 id="true--25"><a class="anchor" href="#true--25"></a>19. 代码生成</h2>
<div class="sectionbody">
<div class="sect2">
<h3 id="true-pojo-md"><a class="anchor" href="#true-pojo-md"></a>19.1. 生成pojo 和 md文件</h3>
<div class="paragraph">
<p>beetsql支持调用SQLManager.gen&#8230;&#8203; 方法生成表对应的pojo类，如：</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">SQLManager sqlManager = new SQLManager(style,loader,cs,new DefaultNameConversion(), new Interceptor[]{new DebugInterceptor()});
//sql.genPojoCodeToConsole("userRole"); 快速生成，显示到控制台
// 或者直接生成java文件
GenConfig config = new GenConfig();
config.preferBigDecimal(true);
config.setBaseClass("com.test.User");
sqlManager.genPojoCode("UserRole","com.test",config);</code></pre>
</div>
</div>
<div class="paragraph">
<p>config 类用来配置生成喜爱,目前支持生成pojo是否继承某个基类, 是否用BigDecimal代替Double,是否采用Date而不是Timestamp来表示日期，是否是直接输出到控制台而不是文件等
生成的代码如下：</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">package com.test;
import java.math.*;
import java.sql.*;
public class UserRole extends com.test.User{
    private Integer id;

    /* 数据库注释 */
    private String userName;
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>也可以自己设定输出模版，通过GenConfig.initTemplate(String classPath),指定模版文件在classpath 的路径，或者直接设置一个字符串模版
GenConfig.initStringTemplate. 系统默认的模版如下：</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-text" data-lang="text">package ${package};
${imports}
/*
* ${comment}
* gen by beetsql ${date(),"yyyy-MM-dd"}
*/
public class ${className} ${!isEmpty(ext)?"extends "+ext} {
	@for(attr in attrs){
	@		if(!isEmpty(attr.comment)){
	//${attr.comment}
	@		}
	private ${attr.type} ${attr.name} ;
	@}

}</code></pre>
</div>
</div>
<div class="paragraph">
<p>如果你需要给beetl模板注册函数等扩展，可以在生成sqlManager.genPojoCode之前，先注册扩展函数</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">SourceGen.gt.registerFunction("xxx",yourFunction)</code></pre>
</div>
</div>
<div class="paragraph">
<p>、
这样就可以再代码模板里使用扩展函数了</p>
</div>
</div>
<div class="sect2">
<h3 id="true--26"><a class="anchor" href="#true--26"></a>19.2. 生成更多的代码</h3>
<div class="paragraph">
<p>可以实现MapperCodeGen的genCode接口，然后添加到 GenConfig里，这样再生成代码后，也会调用自定义的MapperCodeGen来生成更多代码。如系统内置的生成Mapper的代码</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">MapperCodeGen mapper = new MapperCodeGen("com.dao");
config.codeGens.add(mapper);
sql.genPojoCodeToConsole("user", config);</code></pre>
</div>
</div>
<div class="paragraph">
<p>这样，除了生成pojo代码外，还生成mapper代码，内置的mapper代码实现如下，供参考</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">public class MapperCodeGen implements CodeGen {
	String pkg = null;
	public MapperCodeGen(){

	}
	public MapperCodeGen(String pkg){
		this.pkg = pkg;
	}
	public static String mapperTemplate="";
	static {
		mapperTemplate = GenConfig.getTemplate("/org/beetl/sql/ext/gen/mapper.btl");
	}

	@Override
	public void genCode(String entityPkg, String entityClass, TableDesc tableDesc,GenConfig config,boolean isDisplay) {
		if(pkg==null){
			pkg = entityPkg;
		}
		Template template = SourceGen.gt.getTemplate(mapperTemplate);
		String mapperClass = entityClass+"Dao";
		template.binding("className", mapperClass);
		template.binding("package",pkg);
		template.binding("entityClass", entityClass);

		String mapperHead = "import "+entityPkg+".*;"+SourceGen.CR;
		template.binding("imports", mapperHead);
		String mapperCode = template.render();
		if(isDisplay){
			System.out.println();
			System.out.println(mapperCode);
		}else{
			try {
				SourceGen.saveSourceFile(GenKit.getJavaSRCPath(), pkg, mapperClass, mapperCode);
			} catch (IOException e) {
				throw new RuntimeException("mapper代码生成失败",e);
			}
		}


	}

}</code></pre>
</div>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="true-sqlresult"><a class="anchor" href="#true-sqlresult"></a>20. 直接使用SQLResult</h2>
<div class="sectionbody">
<div class="paragraph">
<p>有时候，也许你只需要SQL及其参数列表，然后传给你自己的dao工具类，这时候你需要SQLResult，它包含了你需要的sql，和sql参数。
SQLManager 有如下方法，你需要传入sqlid，和参数即可</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">public SQLResult getSQLResult(String id, Map&lt;String, Object&gt; paras)</code></pre>
</div>
</div>
<div class="paragraph">
<p>paras 是一个map，如果你只有一个pojo作为参数，你可以使用“_root” 作为key，这样sql模版找不到名称对应的属性值的时候，会寻找_root 对象，如果存在，则取其同名属性。</p>
</div>
<div class="paragraph">
<p>SQLResult 如下：</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">public class SQLResult {
	public String jdbcSql;
	public List&lt;Object&gt; jdbcPara;
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>jdbcSql是渲染过后的sql，jdbcPara 是对应的参数值</p>
</div>
</div>
</div>
<div class="sect1">
<h2 id="truehibernate-mybatis-mysql"><a class="anchor" href="#truehibernate-mybatis-mysql"></a>21. Hibernate,MyBatis,MySQL 对比</h2>
<div class="sectionbody">
<div class="paragraph">
<p><a href="http://ibeetl.com/community/?/article/63" class="bare">http://ibeetl.com/community/?/article/63</a>  提供了12项对比并给与评分。在犹豫使用BeetlSQL，可以参考这个全面的对比文章</p>
</div>
</div>
</div>
<div class="sect1">
<h2 id="trueorm"><a class="anchor" href="#trueorm"></a>22. ORM</h2>
<div class="sectionbody">
<div class="sidebarblock">
<div class="content">
<div class="title">注意</div>
<div class="ulist">
<ul>
<li>
<p>BeetlSql的Pojo类与数据库表对应，没有关系映射相关特性，除非Pojo实现@Tail 或者继承TailBean（或者实现Tail接口），额外的关系映射才会放到tail属性里供查询的时候调用。</p>
</li>
<li>
<p>要注意的是，beetlsql的orm 仅仅限于查询结果集，而不包括新增，更新，删除。这些需要调用sqlManager的api直接操作就行了而不像JPA那样还需要成为容器管理对象才能更新</p>
</li>
</ul>
</div>
</div>
</div>
<div class="paragraph">
<p>beetlsql 关系映射是在sql语句里通过orm.single和 orm.many,orm.lazySingle,orm.lazyMany 函数进行申明。beetlsql会根据这些申明来完成关系映射</p>
</div>
<div class="paragraph">
<p>orm.single,orm.many ,orm.lazySingle,orm.lazyMany函数名字本身说明了是一对一，还是一对多或者多对多。以及是直接加载还是懒加载。函数可以放在sql语句任何地方，建议放到头部或者尾部，参数格式有俩种形式，</p>
</div>
<div class="ulist">
<ul>
<li>
<p>使用模板方式查询关系对象，orm.single({"departmentId","id"},"Department") 第一个参数申明了关系映射，即sql查询结果里属性（非字段名)，对应到关系表的查询属性，
如User对象里，departmentId应到Department对象的id，beetlsql会根据此关系发起一次template查询。映射的结果集放在第二个参数Department类里，如果Department与User类在同一个包下，可以省略包名，否则需要加上类包名</p>
</li>
<li>
<p>使用sqlId来查询关系对象，orm.single({"departmentId","id"},"user.selectDepatment","Department")
第一个参数还是映射关系，第二个参数是一sql查询id，beetlsql将查询此sql语句，将结果集放到第三个参数Deparmtent类里</p>
</li>
<li>
<p>lazy 意味着当调用的时候再加载。如果在事务外调用，并不会像hibernate，JPA那样报错，beetlsql会再用一个数据库连接去查询。一般来讲，如果业务代码确定要用，建议不用lazy方式。因为lazy不会有查询优化，性能可能慢一些</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>如上查询关系对象，均放到tail属性里，名称就是类名小写开头，如</p>
</div>
<div class="paragraph">
<p>User user = sqlManager.select("user.selectUserAndDepartment",User.class,paras);
Department dept = user.get("department");</p>
</div>
<div class="paragraph">
<p>如下是个例子，假设user表与department表示一对一关系，user.departmentId对应于deparment.id,因此关系映射是{"departmentId":"id"}
user与 role表示多对多关系，通过user_role做关联</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">selectUserAndDepartment
===
	select * from user where user_id=#userId#
	@ orm.single({"departmentId":"id"},"Department");
	@ orm.many({"id":"userId"},"user.selectRole","Role");

user.selectRole
===

	select r.* from user_role ur left join role r on ur.role_id=r.id

	where ur.user_id=#userId#</code></pre>
</div>
</div>
<div class="paragraph">
<p>java代码的样子:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">User user = sqlManager.select("user.selectUserAndDepartment",User.class,paras);
Department dept = user.get("department");
List&lt;Role&gt; roles = user.get("role");</code></pre>
</div>
</div>
<div class="paragraph">
<p>完整的orm查询例子可以参考 <a href="https://code.csdn.net/xiandafu/beetlsql_orm_sample/tree/master" class="bare">https://code.csdn.net/xiandafu/beetlsql_orm_sample/tree/master</a> 还有文档</p>
</div>
<div class="paragraph">
<p><a href="http://my.oschina.net/xiandafu/blog/735809" class="bare">http://my.oschina.net/xiandafu/blog/735809</a></p>
</div>
<div class="sidebarblock">
<div class="content">
<div class="title">注意</div>
<div class="paragraph">
<p>lazy方式只能继承TailBean才行，如果实现Tail接口，或者@Tail，你取出来的对象并不是你期望的Pojo，而是LazyEntity，你还需要调用get()方法获取到期数据库查询结果
，可以参看TailBean代码来实现你的Lazy支持</p>
</div>
</div>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">public class TailBean implements Tail {
	protected Map&lt;String,Object&gt; extMap = new HashMap&lt;String,Object&gt;();
	boolean hasLazy = false ;
	public Object get(String key){
		if(hasLazy){
			Object o = extMap.get(key);;
			if(o instanceof LazyEntity ){
				LazyEntity lazyEntity = (LazyEntity)o;
				try{
					Object real = lazyEntity.get();
					extMap.put(key, real);
					return real;
				}catch(RuntimeException ex){
					throw new BeetlSQLException(BeetlSQLException.ORM_LAZY_ERROR,"Lazy Load Error:"+key+","+ex.getMessage(),ex);
				}


			}else{
				return o;
			}
		}else{
			return extMap.get(key);
		}

	}

	public void set(String key,Object value){
		if(value instanceof LazyEntity ){
			hasLazy = true;
		}
		this.extMap.put(key, value);

	}</code></pre>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="true-demo"><a class="anchor" href="#true-demo"></a>23. 集成和Demo</h2>
<div class="sectionbody">
<div class="sect2">
<h3 id="truespring-demo"><a class="anchor" href="#truespring-demo"></a>23.1. Spring集成和Demo</h3>
<div class="paragraph">
<p>集成提供了Mapper类的自动注入以及SQLManager的自动注入,以及与spring事务集成</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-xml" data-lang="xml">&lt;!-- DAO接口所在包名，Spring会自动查找其下的类 --&gt;
&lt;bean name="beetlSqlScannerConfigurer" class="org.beetl.sql.ext.spring4.BeetlSqlScannerConfigurer"&gt;
    &lt;!-- 哪些类可以自动注入 --&gt;
    &lt;property name="basePackage" value="org.beetl.sql.ext.spring4"/&gt;
    &lt;!-- 通过类后缀 来自动注入Dao --&gt;
    &lt;property name="daoSuffix" value="Dao"/&gt;
    &lt;property name="sqlManagerFactoryBeanName" value="sqlManagerFactoryBean"/&gt;
&lt;/bean&gt;
&lt;bean id="sqlManagerFactoryBean" class="org.beetl.sql.ext.spring4.SqlManagerFactoryBean"&gt;
   &lt;property name="cs" &gt;
		&lt;bean  class="org.beetl.sql.ext.spring4.BeetlSqlDataSource"&gt;
			&lt;property name="masterSource" ref="dataSource"&gt;&lt;/property&gt;
		&lt;/bean&gt;
	&lt;/property&gt;
    &lt;property name="dbStyle"&gt;
        &lt;bean class="org.beetl.sql.core.db.H2Style"/&gt;
    &lt;/property&gt;
    &lt;property name="sqlLoader"&gt;
		&lt;bean class="org.beetl.sql.core.ClasspathLoader"&gt;
			&lt;property name="sqlRoot" value="/sql"&gt;&lt;/property&gt;
		&lt;/bean&gt;
	&lt;/property&gt;
	 &lt;property name="nc"&gt;
        &lt;bean class="org.beetl.sql.core.UnderlinedNameConversion"/&gt;
    &lt;/property&gt;
    &lt;property name="interceptors"&gt;
        &lt;list&gt;
            &lt;bean class="org.beetl.sql.ext.DebugInterceptor"&gt;&lt;/bean&gt;
        &lt;/list&gt;
    &lt;/property&gt;
&lt;/bean&gt;</code></pre>
</div>
</div>
<div class="ulist">
<ul>
<li>
<p>BeetlSqlScannerConfigurer 根据包名和类后缀来自动注入Dao类,如果没有Dao,可以不配置此项</p>
</li>
<li>
<p>cs: 指定ConnectionSource，可以用系统提供的DefaultConnectionSource，支持按照CRUD决定主从。例子里只有一个master库</p>
</li>
<li>
<p>dbStyle: 数据库类型，目前只支持org.beetl.sql.core.db.MySqlStyle，以及OralceSytle，PostgresStyle，SQLiteStyle，SqlServerStyle，H2Style</p>
</li>
<li>
<p>sqlLoader: sql语句加载来源</p>
</li>
<li>
<p>nc:  命名转化，有默认的DefaultNameConversion，数据库跟类名一致，还有有数据库下划线的UnderlinedNameConversion,JPANameConversion,</p>
</li>
<li>
<p>interceptors:DebugInterceptor 用来打印sql语句，参数和执行时间</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>注意：
任何使用了Transactional 注解的，将统一使用Master数据源，例外的是@Transactional(readOnly=true),这将让Beetsql选择从数据库。</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">@Service
public class MyServiceImpl implements MyService {

	@Autowired
    UserDao dao; // mapper

    @Autowired
    SQLManager sql;

	@Override
	@Transactional()
	public int total(User user) {


		int total = list .size();
		dao.deleteById(User.class, 3);
		User u =new User();
		u.id = 3;
		u.name="hello";
		u.age = 12;
		dao.insert(User.class, u);

		return total;

	}

}</code></pre>
</div>
</div>
<div class="paragraph">
<p>其他集成配置还包括:</p>
</div>
<div class="ulist">
<ul>
<li>
<p>functions  配置扩展函数</p>
</li>
<li>
<p>tagFactorys 配置扩展标签</p>
</li>
<li>
<p>configFileResource  扩展配置文件位置，beetlsql将读取此配置文件覆盖beetlsql默认选项</p>
</li>
<li>
<p>defaultSchema  数据库访问schema</p>
</li>
</ul>
</div>
<div class="sidebarblock">
<div class="content">
<div class="title">参考</div>
<div class="paragraph">
<p>可以参考demo <a href="https://git.oschina.net/xiandafu/springbeetlsql" class="bare">https://git.oschina.net/xiandafu/springbeetlsql</a>
spring boot集成可以参考 demo
<a href="http://git.oschina.net/xiandafu/springboot_beetl_beetlsql，请参考demo配置好" class="bare">http://git.oschina.net/xiandafu/springboot_beetl_beetlsql，请参考demo配置好</a>  META-INF/spring-devtools.properties文件，否则运行不起来</p>
</div>
</div>
</div>
</div>
<div class="sect2">
<h3 id="truejfinal-demo"><a class="anchor" href="#truejfinal-demo"></a>23.2. JFinal集成和Demo</h3>
<div class="paragraph">
<p>在configPlugin 里配置BeetlSql</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">JFinalBeetlSql.init();</code></pre>
</div>
</div>
<div class="paragraph">
<p>默认会采用c3p0 作为数据源，其配置来源于jfinal 配置，如果你自己提供数据源或者主从，可以如下</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">JFinalBeetlSql.init(master,slaves);</code></pre>
</div>
</div>
<div class="paragraph">
<p>由于使用了Beetlsql，因此你无需再配置 <strong>数据库连接池插件，和ActiveRecordPlugin</strong>,可以删除相关配置。</p>
</div>
<div class="paragraph">
<p>在controller里，可以通过JFinalBeetlSql.dao 方法获取到SQLManager</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">SQLManager dao = JFinalBeetlSql.dao();
		BigBlog blog = getModel(BigBlog.class);
		dao.insert(BigBlog.class, blog);</code></pre>
</div>
</div>
<div class="paragraph">
<p>如果想控制事物，还需要注册Trans</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">public void configInterceptor(Interceptors me) {
	me.addGlobalActionInterceptor(new Trans());
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>然后业务方法使用</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">@Before(Trans.class)
public void doXXX(){....}</code></pre>
</div>
</div>
<div class="paragraph">
<p>这样，方法执行完毕才会提交事物，任何RuntimeException将回滚，如果想手工控制回滚.也可以通过</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java" data-lang="java">Trans.commit()
Trans.rollback()</code></pre>
</div>
</div>
<div class="paragraph">
<p>如果习惯了JFinal Record模式，建议用户创建一个BaseBean，封装SQLManager CRUD 方法即可。然后其他模型继承此BaseBean</p>
</div>
<div class="sidebarblock">
<div class="content">
<div class="title">注意</div>
<div class="paragraph">
<p>可以通过jfinal属性文件来配置sqlManager，比如
PropKit.use("config.txt", "UTF-8"),然后可以配置
sql.nc,sql.root，sql.interceptor，sql.dbStyle，具体参考源代码</p>
</div>
<div class="paragraph">
<p>JFinalBeetlSql.initProp</p>
</div>
</div>
</div>
<div class="sidebarblock">
<div class="content">
<div class="title">参考</div>
<div class="paragraph">
<p>可以参考demo <a href="https://git.oschina.net/xiandafu/jfinal_beet_beetsql_btjson" class="bare">https://git.oschina.net/xiandafu/jfinal_beet_beetsql_btjson</a>
demo <a href="https://code.csdn.net/xiandafu/beetlsql_orm_sample/tree/master" class="bare">https://code.csdn.net/xiandafu/beetlsql_orm_sample/tree/master</a></p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<div id="footer">
<div id="footer-text">
Last updated 2016-09-30 09:31:05
</div>
</div>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/8.9.1/styles/github.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/8.9.1/highlight.min.js"></script>
<script>hljs.initHighlighting()</script>
</body>
</html>